The if update(column_name) clause 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 does not.
For example, suppose you create the following table:
create table junk (a int null, b int not null)
and then write the following trigger:
create trigger junktrig
on junk
for insert
as
if update(a) and update(b)
print "FIRING"
/*"if update" is true for both columns.
The trigger is activated.*/
insert junk (a, b) values (1, 2)
/*"if update" is true for both columns.
The trigger is activated.*/
insert junk values (1, 2)
/*Explicit NULL:
"if update" is true for both columns.
The trigger is activated.*/
insert junk values (NULL, 2)
/* If default exists on column a,
"if update" is true for either column.
The trigger is activated.*/
insert junk (b) values (2)
/* If no default exists on column a,
"if update" is not true for column a.
The trigger is not activated.*/
insert junk (b) values (2)
The same results would be produced using only the clause:
if update(a)
To create a trigger that disallows the insertion of implicit nulls, you can use:
if update(a) or update(b)
SQL statements in the trigger can then test to see if a or b is null.
