|
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" ? |
|
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... stack_trace() helped. Thank.
(31 Oct '19, 07:01)
Stalker
|
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;
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...
No, I did not test it, it was just a hint!