I have laid out the scenario below. Essentially my question is why does a FK
delete restriction prevent my update trigger from working? ... I'm not
deleting I am updating.
****************************************************************************
*
[Child table has foreign key defined as:]
FOREIGN KEY "FK_RMAITEM_RMA" ("RMA_ID") REFERENCES "RMA"("RMA_ID") ON DELETE
RESTRICT
[Parent table has a trigger defined as:]
CREATE TRIGGER UPD_RMA FOR RMA AFTER UPDATE EXECUTE (
TRY
IF NEW.ACTIVE <> OLD.ACTIVE THEN
UPDATE RMA_ITEM SET ACTIVE = :NEW.ACTIVE WHERE RMA_ID = :NEW.RMA_ID;
IF NEW.RMA_ID <> OLD.RMA_ID THEN
BEGIN
UPDATE RMA_ITEM SET RMA_ID = :NEW.RMA_ID WHERE RMA_ID = :OLD.RMA_ID;
UPDATE RMA_ITEM SET RMA_ITEM_ID = CHR(:NEW.RMA_ID) & RMA_ITEM.SERIAL WHERE
RMA_ID = :OLD.RMA_ID;
END;
CATCH
IF $RC <> 100 THEN
STOP ($RC, 'UPATE RMA_ITEM ERROR');
)
If the RMA_ID changes then it should update the child table but the trigger
fails with error:
Integrity constraint violation;350 POS(1) Referential integrity
violated:FK_RMAITEM_RMA,RMA
UPDATE RMA SET RMA_ID = 205002 WHERE RMA_ID = 2050002
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
