cancel
Showing results for 
Search instead for 
Did you mean: 

How to distinguish the reason for deleting a row in a trigger ?

1,537

Hi All,

There are two tables

MASTER
  ID integer
  CSTR1 char (10)

Detail
  ID integer
  MASTER_ID integer
  CSTR2 char (10)

They are connected by a key:

ALTER TABLE "DETAIL" ADD CONSTRAINT "FK_MASTER" NOT NULL FOREIGN KEY ("MASTER_ID" ASC) REFERENCES "MASTER" ("ID") ON DELETE CASCADE

That is, when a "MASTER" line is deleted, all its "DETAIL" lines are automatically deleted.

The "DETAIL" table has a trigger:

CREATE TRIGGER "DetailTrigger" BEFORE INSERT, UPDATE, DELETE
ORDER 1 ON "DETAIL"
REFERENCING OLD as old_f NEW AS new_f
FOR EACH ROW
BEGIN
< ... >
END

The user has two possibilities (two situations):

1) Delete individual lines in "DETAIL". 2) Delete the line in "MASTER". In this case, all “DETAIL” lines of this “MASTER” are deleted by the key.

Question: How can one distinguish between these two situations in "DetailTrigger" ?

Baron
Participant
0 Kudos

maybe so?

if exists (select 1 from MASTER where ID = old_f.ID) then

-- delete coming from DETAIL

else

-- delete coming from MASTER

end if;

VolkerBarth
Contributor
0 Kudos

Are you sure that does work as expected? IMHO, it will highly depend on the current isolation_level, and when the DELETE acually was cascaded from the MASTER table, a SELECT with isolation_level >= 1 will block, if I'm not mistaken...

Baron
Participant
0 Kudos

No, I did not test it, it was just a hint!

Accepted Solutions (0)

Answers (1)

Answers (1)

VolkerBarth
Contributor

I have not used that myself but you could probably include calls to the stack_trace() system function or the similar sa_strack_trace() system procedure to distinguish whether your trigger is called from within an immediate DELETE statement or from a chained calls.

Say, something like:

message 'Trigger stack: ' || stack_trace('caller', 'stack+sql')  to log;

should log the according DELETE statement on "DETAIL" if that has been called directly, and otherwise something different...

0 Kudos

stack_trace() helped. Thank.