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: Update Trigger Statement
Date Thu, 28 Sep 2006 16:47:57 GMT
Hi,

I simplified your schema a little bit to demonstrate an update trigger statement that will
work
except it does not enforce any kind of foreign key constraint on the tables that contain
the column staffEmail.  What this means is if you update the staffEmail column in the STAFF
table
this trigger works, but if you update it directly in the other tables referential integrity
is not
enforced.

Here's the example:

ij> CREATE TABLE STAFF
(
staffEmail VARCHAR(50) NOT NULL,
staffLastName VARCHAR(20) NOT NULL,
CONSTRAINT STAFF_EMAIL_PK PRIMARY KEY (staffEmail));
0 rows inserted/updated/deleted

CREATE TABLE DEPARTMENTS
(
departmentName VARCHAR(70) NOT NULL,
staffEmail VARCHAR(50) NOT NULL,
CONSTRAINT DEPARTMENT_PK PRIMARY KEY (departmentName));
0 rows inserted/updated/deleted

-- you can expand this trigger to set the staffemail
-- to the value of the staffemail in the departments
-- table for the other tables you mentioned below
-- in your original trigger statement too.

ij> CREATE TRIGGER STAFF_EMAIL_UPDATE
AFTER UPDATE
ON STAFF
REFERENCING OLD AS OLDROW NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
UPDATE DEPARTMENTS
SET STAFFEMAIL = NEWROW.STAFFEMAIL
WHERE STAFFEMAIL = OLDROW.STAFFEMAIL
AND OLDROW.STAFFEMAIL <> NEWROW.STAFFEMAIL;
0 rows inserted/updated/deleted

ij> insert into staff values ('me@myuniv.edu', 'lastname');
1 row inserted/updated/deleted
ij> insert into staff values ('you@youruniv.edu', 'yourname');
1 row inserted/updated/deleted
ij> insert into departments values ('math', 'you@youruniv.edu');
1 row inserted/updated/deleted
ij> insert into departments values ('english', 'eprof@youruniv.edu');
1 row inserted/updated/deleted

ij> select * from staff;
STAFFEMAIL                                        |STAFFLASTNAME
-----------------------------------------------------------------------
me@myuniv.edu                                     |lastname
you@youruniv.edu                                  |yourname
2 rows selected

ij> select * from departments;
DEPARTMENTNAME                                    |STAFFEMAIL
--------------------------------------------------------------------------------
math                                              |you@youruniv.edu
english                                           |eprof@youruniv.edu
2 rows selected
ij> update staff set staffEmail = 'yournew@youruniv.edu' where staffLastName = 'yourname';
1 row inserted/updated/deleted

ij> select * from staff;
STAFFEMAIL                                        |STAFFLASTNAME
-----------------------------------------------------------------------
me@myuniv.edu                                     |lastname
yournew@youruniv.edu                              |yourname
2 rows selected
ij> select * from departments;
DEPARTMENTNAME                                    |STAFFEMAIL
--------------------------------------------------------------------------------
math                                              |yournew@youruniv.edu
english                                           |eprof@youruniv.edu
2 rows selected

ij> -- what does not work is if you update staffemail in depts directly
update departments set staffemail = 'different@youruniv.edu' where staffemail =
'yournew@youruniv.edu';
1 row inserted/updated/deleted
ij> select * from departments;
DEPARTMENTNAME                                           |STAFFEMAIL
--------------------------------------------------------------------------------
math                                                      |different@youruniv.edu
english                                                   |eprof@youruniv.edu
2 rows selected

ij> select * from staff;
STAFFEMAIL                                        |STAFFLASTNAME
-----------------------------------------------------------------------
me@myuniv.edu                                     |lastname
yournew@youruniv.edu                              |yourname
2 rows selected
ij>

Susan

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

> 
> I am very new to Derby DB and I'm having a problem with the Update Trigger
> Statement, although I keep getting an error; do I need to declare a Foreign
> Key (staffEmail) in the DEPARTMENTS table? I would greatly appreciate your
> help in this matter:
> 
> -- 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));
> 
> -- If STAFF (staffEmail) PRIMARY KEY changes it must be updated in tables
> -- DEPARTMENTS (staffEmail)
> -- MODULES (staffEmail)
> -- STUDENTS (staffEmail)
> CREATE TRIGGER trig01
> AFTER UPDATE OF departmentName ON STAFF REFERENCING OLD AS UPDATEDROW FOR
> EACH ROW MODE DB2SQL INSERT INTO DEPARTMENTS VALUES
> (UPDATEDROW.departmentName, UPDATEDROW.departmentTelephoneNo,
> UPDATEDROW.departmentExtensionNo, UPDATEDROW.departmentFax,
> UPDATEDROW.departmentEmail, UPDATEDROW.departmentCampus,
> UPDATEDROW.departmentBuilding, UPDATEDROW.staffEmail); 
> 
> ?UPDATEDROW.DEPARTMENTNAME? (42X04) is either not in any table in the FROM
> list or appears within a join specification and is outside the scope of the
> join specification or appears in a HAVING clause and is not in the GROUP BY
> list. If this is a CREATE or ALTER statement then
> ?UPDATEDROW.DEPARTMENTNAME? is not a column in the target table.
> 
> 
> 
> -- 
> View this message in context:
> http://www.nabble.com/Update-Trigger-Statement-tf2350005.html#a6543690
> Sent from the Apache Derby Developers mailing list archive at Nabble.com.
> 
> 


Mime
View raw message