db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <karth...@gmail.com>
Subject Re: Update Trigger Statement
Date Thu, 28 Sep 2006 15:27:54 GMT

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


Mime
View raw message