Activating DML Triggers with Implicit and Explicit Null Values
Inserting an explicit null value into a column or using the DEFAULT keyword to assign a value to a column activates a DML trigger as expected. Similarly, when no value is specified in the INSERT statement for a column, a DML trigger is still activated when:
An implicit null value is inserted into a column because no DEFAULT definition exists.
A default value is inserted into a column because a DEFAULT definition does exist.
Examples
A. Testing DML trigger activation with null and default values
The following examples show how a DML trigger is affected by implicit and explicit null values. A small table is created to hold two integer values. One column can contain null values; the other column contains a default value. A DML trigger evaluates whether the both columns are modified, and displays a message when the trigger is activated. A series of INSERT statements tests trigger activation by inserting combinations of implicit and explicit null values.
CREATE TABLE t1
(a int NULL, b int NOT NULL DEFAULT 99)
GO
CREATE TRIGGER t1trig
ON t1
FOR INSERT, UPDATE
AS
IF UPDATE(a) AND UPDATE(b)
PRINT 'FIRING'
GO
--When two values are inserted, the UPDATE is TRUE for both columns and the trigger is activated.
INSERT t1 (a, b)
VALUES (1, 2)
--When two values are updated, the UPDATE is TRUE for both columns and the trigger is activated.
UPDATE t1
SET a = 1, b = 2
--When an explicit NULL is inserted in column a, the UPDATE is TRUE for both columns and the trigger is activated.
INSERT t1
VALUES (NULL, 2)
--When an explicit NULL is updated in column a, the UPDATE is TRUE for both columns,the trigger is activated.
UPDATE t1
SET a = NULL, b = 2
--When an implicit NULL is inserted in column a, the UPDATE is TRUE for both columns and the trigger is activated.
INSERT t1 (b)
VALUES (2)
--When column a is updated with an implicit NULL, the UPDATE is FALSE for both columns and the trigger is not activated.
UPDATE t1
SET b = 2
--When the default value is implicitly inserted in column b, the UPDATE is TRUE for both columns and the trigger is activated.
INSERT t1 (a)
VALUES (2)
--When column b is updated with an implicit NULL, the UPDATE is FALSE for both columns and the trigger is not activated.
UPDATE t1
SET a = 2
--When the default value is explicitly inserted in column b, the UPDATE is TRUE for both columns and the trigger is activated.
INSERT t1 (a, b)
VALUES (2, DEFAULT)
--When column b is updated explicitly with the default value, the UPDATE is TRUE for both columns and the trigger is activated.
UPDATE t1
SET a = 2, b = DEFAULT