Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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.2k361549822
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,170 times

last updated: 31 Oct '19, 07:01