Consultor Eletrônico

Solution ID: P18610

GOAL:

Implementation of Delete Cascade on Tables does not work via Triggers when there is constraint ?

CAUSE:

This is not working because Progress SQL-92 evaluates constraints before executing Triggers.

Example that shows this behavior :

Creation of Tables :
===================
create table PUB.CUSTOMER (
CUSTOMER_ID integer not null,
NAME char(80),
constraint PK_CUSTOMER primary key (CUSTOMER_ID)
);

create table PUB.ORDER(
ORDER_ID integer not null,
ORDATE date not null default SYSDATE,
CUSTOMER_ID integer not null,
constraint PK_ORDER primary key(ORDER_ID),
constraint FK_CUSTOMER foreign key(CUSTOMER_ID)
references PUB.CUSTOMER
);

Insert or ROWS
==============
insert into PUB.CUSTOMER (CUSTOMER_ID, NAME) values (2,'raza');
insert into PUB.ORDER (ORDER_ID, CUSTOMER_ID) values (2,2);
commit;


Create Trigger
==============
CREATE TRIGGER PUB.CUSTOMER_TRG_DELB
BEFORE DELETE ON PUB.CUSTOMER
REFERENCING OLDROW
FOR EACH ROW
IMPORT
import java.sql.*;
BEGIN
SQLIStatement del = new SQLIStatement("delete from PUB.ORDER where CUSTOMER_ID = ?");
del.setParam(1, OLDROW.getValue(1, INTEGER));
del.execute();
END
commit;

Delete records in the Customer:
=========================
delete from pub.customer where customer_id = 2

Error returned :
===========
The error [JDBC Progress Driver]:Constraint violation (7597) is returned.

FIX:

The current order of execution is not a bug, it is a design choice which ensures
that integrity constraints are always enforced, and that a BEFORE trigger sees
a consistent database state.

In the SQL:1999 standard, where triggers were standardized, BEFORE triggers are not allowed to see the row deleted or to change the database. And, the order of constraints and BEFORE triggers is not defined. The order of execution of constraints and AFTER triggers is defined in SQL:1999: constraints execute before the AFTER trigger.