SAP SQL Anywhere

Question

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.

Answer 1

2

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 Barth
40.5k●365●556●827
accept rate: 34%

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

flat view

SAP SQL Anywhere

determine why trigger fired

Follow this question

Related questions