Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

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.

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

Community Additions

ADD
Show:
© 2015 Microsoft