I try to implement a referential constraint "ON UPDATE CASCADE" with a trigger. If somebody says SAPDB has this constraint included, please let me know (I know only "ON DELETE CASCADE").
I have to update more than one table after updating the "Master-Table".
Trigger 1 (Update one dependent table - WORKS):
CREATE TRIGGER triggername FOR table_master AFTER UPDATE
EXECUTE (
TRY
IF ( NEW.ColA = 'Value' ) THEN BEGIN
UPDATE user.table_a SET ColB = :NEW.ColumnB, ColC = :NEW.ColumnC
WHERE ColB = :OLD.ColumnB AND ColC = :OLD.ColumnC;
END;
CATCH
IF $RC <> 100
THEN STOP ($RC, 'Unexpected error');
)Trigger 2 (Update two or more dependent table - DON'T WORK):
CREATE TRIGGER triggername FOR table_master AFTER UPDATE
EXECUTE (
TRY
IF ( NEW.ColA = 'Value' ) THEN BEGIN
UPDATE user.table_a SET ColB = :NEW.ColumnB, ColC = :NEW.ColumnC
WHERE ColB = :OLD.ColumnB AND ColC = :OLD.ColumnC;
UPDATE user.table_b SET ColB = :NEW.ColumnB, ColC = :NEW.ColumnC
WHERE ColB = :OLD.ColumnB AND ColC = :OLD.ColumnC;
END;
CATCH
IF $RC <> 100
THEN STOP ($RC, 'Unexpected error');
)The difference is in Trigger 1 there is only one Update-Statement and in Trigger 2 there are two or more Update-Statements.
I can store each triggers in the DB (only one at the same time), but only trigger 1 produces a result.
Btw I use SAPDB 7.3.0 .
Best regards
Hannes
--
Hannes Degenhart GPS - Gesellschaft zur Pruefung von Software mbH
Hoervelsinger Weg 54 D - 89081 Ulm - Germany
Pho. +49 731 96657 14 Fax. +49 731 96657 57
mailto:[EMAIL PROTECTED] Web: http://www.gps-ulm.de
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
