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. With an implicit NULL, if the value is not specified by the query or by the assigned default, the trigger on that column is not activated.

CREATE TABLE junk
(a int NULL, b int NOT NULL)
go
CREATE TRIGGER junktrig
ON junk
FOR INSERT
AS
IF UPDATE(a) AND UPDATE(b)
    PRINT 'FIRING'
go
CREATE DEFAULT b_dflt
AS 99
go
sp_bindefault b_dflt, junk.b
go
INSERT junk (a, b)        /*IF UPDATE is true for both columns, */
VALUES (1, 2)            /*the trigger is activated. */
INSERT junk                /*IF UPDATE is true for both columns, */
VALUES (1, 2)            /*the trigger is activated. */
INSERT junk                /*Explicit NULL: */
VALUES (null, 2)            /*IF UPDATE is true for both columns, */
                        /*the trigger is activated. */
INSERT junk (b)            /*No default on column a, */
VALUES (2)                /*IF UPDATE is not true for both columns, */
                        /*the trigger is not activated. */
INSERT junk (a)            /*Default on column b, */
VALUES (2)                /*IF UPDATE is true for both columns, */
                        /*the trigger is activated. */

Exactly the same results are produced by using only this clause:

IF UPDATE(a)

To create a trigger that disallows the insertion of implicit null values, use:

IF UPDATE(a) OR UPDATE(b)

SQL statements in the trigger can then test to see whether a is NULL.