Hi Listmembers!
I am having problems to create a delete-trigger.
Here is the example:
CREATE TABLE address (
address_ID FIXED(10) DEFAULT SERIAL PRIMARY KEY,
street VARCHAR(20),
city VARCHAR(20),
zip VARCHAR(5),
region VARCHAR(2))
//
CREATE TABLE customer (
customer_id FIXED(10) DEFAULT SERIAL PRIMARY KEY,
name VARCHAR(20),
addressID FIXED(10),
invoiceaddressID FIXED(10),
FOREIGN KEY (addressID) REFERENCES address (address_id),
FOREIGN KEY (invoiceaddressID) REFERENCES address (address_id))
//
INSERT INTO address (street, city, zip)
VALUES ('Samplestreet 1','Sampletown','11111')
//
INSERT INTO address (street, city, zip)
VALUES ('Sampleway 2','Samplecity','22222')
//
INSERT INTO customer (name, addressID, invoiceaddressID)
Now I try to define a delete-trigger:
CREATE TRIGGER customerdeletion FOR customer AFTER DELETE EXECUTE (
TRY
IF OLD.addressID <> NULL
THEN DELETE FROM address WHERE address_id = :OLD.addressID;
IF OLD.invoiceaddressID <> NULL
THEN DELETE FROM address WHERE address_ID = :OLD.invoiceaddressID;
CATCH
IF $rc <> 100
THEN STOP ($rc, 'Customer deletion failed');
)
But when I commit the create-trigger statement I always get the following
error:
---- Error -------------------------------
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
General error;-8010 POS(83) Table name must be in from list
Marked is 'OLD.addressID' in the first IF-Statement.
Since I tried it for several hours I have no idea what is wrong!?
Can somone please give me a hint - thanks a lot!
BTW: I am using MaxDB 7.6.00.10 and SQL Studio 7.6.00.03 on a Windows based
notebook (XP-Home, SP2, localized german)
Thanks for help and comments!
Regards,
Andreas
--
5 GB Mailbox, 50 FreeSMS http://www.gmx.net/de/go/promail
+++ GMX - die erste Adresse für Mail, Message, More +++
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
