db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Susan L. Cline" <home4...@pacbell.net>
Subject Re: Stored Procedure and Update Trigger Statement
Date Thu, 28 Sep 2006 16:59:00 GMT
Hi,

I'm a little confused what is going on here.  Once you look at my response to your
email titled 'Update Trigger Statement' will you tell me if that answers your question?

Here is a guess at what you are trying to do;

If someone inserts a value into the staffEmail in the staff table it is also
added to the other tables.  Is this working for you currently?

If someone updates a staffEmail in the staff table you would like all of the other
tables that contain this staffEmail to be updated as well.  This would work with the
update trigger I had in the other email you sent for the departments table.  I believe
you will need to create a similar trigger for all of the other tables whose staffEmail
you would like updated.

What will not work is if someone updates the staffEmail directly in the other tables (not
the
Staff table).

Please let me know if this answers your question.

Also, hopefully someone else may be able to suggest a solution or understand the problem better
than I do.

Regards,

Susan

--- tassz <tsmall@j7isstudy.com> wrote:

> 
> I have another problem with the Update Trigger Statement.. 
> 
> For example; in the STAFF table below if the user (Head of Department)
> inputs a 'Staff Email' it is stored in the STAFF table and also in the
> DEPARTMENTS table; although for example, there could be 100 Staff Emails but
> only 1 member of staff is a Head of a Department. 
> 
> If the user (Head of Department) 'Staff Email' is changed, the DB checks to
> see if that 'Staff Email' is present in the DEPARTMENTS table - if it is
> then the 'Staff Email' is UPDATED in the DEPARTMENTS table or if it is not
> in the DEPARTMENTS table, it is  not UPDATED. 
> 
> I hope that this is understandable... I need to know how to write a Stored
> Procedure that can do the above within an Update Trigger Statement - Can you
> help me on this matter? Thanks again!
> 
> -- Create a table to store staff details CREATE TABLE STAFF ( staffEmail
> VARCHAR(50) NOT NULL, staffPassword VARCHAR(20) NOT NULL, staffHashPassword
> VARCHAR(255) NOT NULL, staffTitle CHAR(5) NOT NULL, staffFirstName
> VARCHAR(20) NOT NULL, staffLastName VARCHAR(20) NOT NULL, staffTelephoneNo
> VARCHAR(20) NOT NULL, staffExtensionNo VARCHAR(10), staffFax VARCHAR(20),
> staffBuilding VARCHAR(50) NOT NULL, staffRoom VARCHAR(30) NOT NULL,
> staffAccountActivated INT NOT NULL, CONSTRAINT STAFF_EMAIL_PK PRIMARY KEY
> (staffEmail));
> 
>  
> 
> -- Create a table to store department details CREATE TABLE DEPARTMENTS (
> departmentName VARCHAR(70) NOT NULL, departmentTelephoneNo VARCHAR(20) NOT
> NULL, departmentExtensionNo VARCHAR(10), departmentFax VARCHAR(20),
> departmentEmail VARCHAR(50) NOT NULL, departmentCampus VARCHAR(50),
> departmentBuilding VARCHAR(50) NOT NULL, staffEmail VARCHAR(50) NOT NULL,
> CONSTRAINT DEPARTMENT_PK PRIMARY KEY (departmentName));
> 
> 
> -- 
> View this message in context:
> http://www.nabble.com/Stored-Procedure-and-Update-Trigger-Statement-tf2350644.html#a6545503
> Sent from the Apache Derby Developers mailing list archive at Nabble.com.
> 
> 


Mime
View raw message