on 2019 Oct 29 6:27 AM
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
69 | |
10 | |
9 | |
6 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.