The goal is to create a delete trigger that copies data to an archive table.. If data already exists for a rept_id, it should delete it first.

In this case, the test is delete from rept_css_asc where rept_id = -1 andthere are 2 records in rept_css_asc, but it fires each row and for each statement does not give provide variables. Fails on reference to o.rept_id. I prefer not to use the <B> bolded </B> area.

How do I prevent the trigger from deleting too much

CREATE TRIGGER "USERID"."REPT_CSS_ARCH_INS"
AFTER
DELETE
ON "USERID"."REPT_CSS_ASC"
REFERENCING OLD as "O"
FOR EACH ROW
MODE DB2SQL

begin atomic

delete from userid.rept_css_asc_archive where rept_id = o.REPT_ID
<B> and deleted_date < current timestamp </B>;

insert into userid.rept_css_asc_archive ( REPT_ID,css_id,DELETED_DATE )
values ( o.REPT_ID,o.css_id, current timestamp);

end~

Added a check for the deleted date... See below and it only deletes the previous records.

CREATE TRIGGER "USERID"."REPT_CSS_ARCH_INS"
AFTER
DELETE
ON "USERID"."REPT_CSS_ASC"
REFERENCING OLD as "O"
FOR EACH ROW
MODE DB2SQL

begin atomic

delete from userid.rept_css_asc_archive a where a.rept_id = o.REPT_ID and a.deleted_date < current timestamp ;

insert into userid.rept_css_asc_archive ( REPT_ID,css_id,DELETED_DATE )
values ( o.REPT_ID,o.css_id, current timestamp);

end~