db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tassz <tsm...@j7isstudy.com>
Subject Stored Procedure and Update Trigger Statement
Date Thu, 28 Sep 2006 12:31:26 GMT

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