Inserting and Updating Triggers

When an insert or update command executes, the SAP ASE server simultaneously adds rows to both the trigger table and the inserted table. The rows in the inserted table are always duplicates of one or more rows in the trigger table.

An update or insert trigger can use the if update command to determine whether the update or insert changed a particular column. if update (column_name) is true for an insert statement whenever the column is assigned a value in the select list or in the values clause. An explicit NULL or a default assigns a value to a column and thus activates the trigger. An implicit NULL, however, does not.

For example, if you create the following table and trigger:

create table junk 
 (aaa int null, 
bbb int not null)

create trigger trigtest on junk
for insert as 
if update (aaa) 
    print "aaa updated"
if update (bbb)
    print "bbb updated"

Inserting values into either column or into both columns fires the trigger for both column aaa and column bbb:

insert junk (aaa, bbb) 
values (1, 2)

aaa updated
bbb updated

Inserting an explicit NULL into column aaa also fires the trigger:

insert junk 
values (NULL, 2)

aaa updated
bbb updated

If there was a default for column aaa, the trigger would also fire.

However, with no default for column aaa and no value explicitly inserted, the SAP ASE server generates an implicit NULL and the trigger does not fire:

insert junk (bbb) 
values (2)

bbb updated

if update is never true for a delete statement.