If I have a trigger defined to fire "BEFORE INSERT,UPDATE", is there a way to determine whether the event is question is an INSERT or UPDATE? The table involved has at least one field defined as "not null", so at first blush I'm tempted to use something like this:

CREATE TRIGGER MyTrigger BEFORE insert, update on MyTable
referenceing old as OldRec new as NewRec
for each row
begin
  declare isInsert char(3);
  if OldRec.RequiredField is null then
    isInsert = 'Yes';
  else
    isInsert = 'No';
  end if
end

I've looked to see if there is a built-in variable I can reference to make the determination, but my searching powers are apparently too weak. We are using SQL/Anywhere 16.

asked 18 Dec '15, 10:23

BudDurland's gravatar image

BudDurland
3169920
accept rate: 0%

edited 18 Dec '15, 10:23


Yes, you can use the "Trigger operation conditions" to tell an INSERT from an UPDATE.

More on this is also discussed in that other FAQ.

Note that your proposed code won't work: There's no OLD row to check in an INSERT statement - IMHO, the operation will fail, and so will the INSERT, either.

If you simply want to fill a NOT NULL column with a value, you simply can do so in a BEFORE INSERT trigger, as the NOT NULL check will be done later, so you simply might use:

if NewRec.RequiredField is null then
   set NewRec.RequiredField = <whatever>;
end if;

FWIW, here's an old posting by Glenn Paulley with detailed insights (as expected...) on the internal steps a trigger has to consider - cf. that old NNTP article "Before update trigger and computed column " - I'm not sure whether it is fully true for current versions, however, a BEFORE TRIGGER surely is run before NULLs in NOT NULL columns are checked.

permanent link

answered 18 Dec '15, 10:54

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

edited 18 Dec '15, 11:06

The actual objective is to (perhaps) perform operations on other tables depending on the cause of the trigger, not force a value in a required field. As you suggested, we are already using BEFORE insert/update, or default field values, for that

(19 Dec '15, 14:04) Bud Durland MRP
Replies hidden

So, which "Bud Durland" is the one after the BEFORE UPDATE trigger - the one with or without the " MRP" suffix? :)

(21 Dec '15, 07:46) Volker Barth

It depends on which browser I'm using. :) I had trouble with the SAP login/authentication process some months ago and ended up with two logins & two certs from SAP.

(21 Dec '15, 07:55) Bud Durland MRP
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:

×61

question asked: 18 Dec '15, 10:23

question was seen: 429 times

last updated: 21 Dec '15, 07:55