db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tassz <tsm...@j7isstudy.com>
Subject Update Trigger Statement
Date Thu, 28 Sep 2006 10:08:50 GMT

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