You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by tassz <ts...@j7isstudy.com> on 2006/09/28 12:08:50 UTC
Update Trigger Statement
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.
Re: Update Trigger Statement
Posted by Rajesh Kartha <ka...@gmail.com>.
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.
>
>
>
>
>
Re: Update Trigger Statement
Posted by "Susan L. Cline" <ho...@pacbell.net>.
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 <ts...@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.
>
>