From: Andreas Goldstein (accountistnurzumleerengedachtgmx.li)
Date: Tue Aug 16 2005 - 14:08:48 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]