My first thoughts on the query:
The
AFTER UPDATE OF departmentName ON *STAFF *REFERENCING OLD AS UPDATEDROW
in the trigger statement will refer to the row on the table being
updated - in the your case the STAFF table.
The statement in the trigger
AFTER UPDATE OF departmentName ON STAFF
I don't see a column called 'departmentName' in table STAFF. Also all
the columns referenced
using UPDATEDROW in the INSERT section of the trigger belong to the
DEPARTMENTS table, whereas the
trigger is on STAFF and only columns of STAFF can be referenced using
the UPDATEDROW alias.
More on triggers: http://db.apache.org/derby/docs/dev/ref/rrefsqlj43125.html
HTH,
-Rajesh
tassz 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.
