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 this table:
create table junk (a int null, b int not null)
then write this 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 are produced using only this 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.
