Stored Procedure and Update Trigger Statement

[tassz <ts...@j7isstudy.com>]

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.

Re: Stored Procedure and Update Trigger Statement

["Susan L. Cline" <ho...@pacbell.net>]

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 <ts...@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.
> 
>