Implicit and Explicit Null Values

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.