From: Florian Schmitz (fschmitzacardo.com)
Date: Wed Jan 25 2006 - 04:36:14 CST

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

Hello list,

i'm quite amused by my own stupidity while trying to create the following trigger :

CREATE TRIGGER ECOUPON_UPDATE FOR ECOUPON AFTER UPDATE EXECUTE (
TRY
IF NEW.INVOICE_UUID IS NOT NULL AND NEW.CREDIT_UUID IS NOT NULL THEN BEGIN
     DELETE FROM SCHEMA1.AGG_INVOICE WHERE UUID = :OLD.UUID;
  END;

IF NEW.INVOICE_UUID IS NULL OR NEW.CREDIT_UUID IS NULL THEN BEGIN
     INSERT INTO SCHEMA1.AGG_INVOICE SELECT * FROM SCHEMA1.ECOUPON WHERE UUID = :NEW.UUID;
END;

CATCH
if $RC <> 100 THEN
STOP($RC, 'unexpected error in trigger');
)

table ecoupon :
CREATE TABLE "SCHEMA1"."ECOUPON"
(
        "UUID" Varchar (50) ASCII NOT NULL,
        "INVOICE_UUID" Integer,
        "CREDIT_UUID" Integer,
        PRIMARY KEY ("UUID")
)

table agg_invoice is an identical copy of ecoupon with identical data.

When executing an update on ecoupon whch sets both invoice_uuid and credit_uuid to non-null-values,
the first if-clause is executed, but the delete-statement obviously isn't executed (Tested with
STOP(..)).

The second if-clause is working as needed.
What am i missing?

Thanks,

Flo

--

Florian Schmitz

______________________
acardo technologies AG
Königswall 18a
44137 Dortmund

fon: +49 (0) 2 31 / 58 44 97 - 0
fax: +49 (0) 2 31 / 58 44 97 - 21
mail: fschmitzacardo.com
web: www.acardo.com

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

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