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" ?

asked 29 Oct '19, 06:27

Stalker's gravatar image

Stalker
515293151
accept rate: 11%

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;

(29 Oct '19, 08:09) Baron
Replies hidden

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...

(29 Oct '19, 10:41) Volker Barth

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

(29 Oct '19, 10:58) Baron

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...

permanent link

answered 29 Oct '19, 08:07

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

converted 31 Oct '19, 08:35

stack_trace() helped. Thank.

(31 Oct '19, 07:01) Stalker
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×79
×22
×13

question asked: 29 Oct '19, 06:27

question was seen: 1,144 times

last updated: 31 Oct '19, 07:01