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 the trigger as expected. Similarly, when no value is specified in the INSERT statement for a column, the trigger is still activated when:

Examples

CREATE TABLE junk

(a int NULL, b int NOT NULL DEFAULT 99)

GO

  

CREATE TRIGGER junktrig

ON junk

FOR INSERT, UPDATE

AS

IF UPDATE(a) AND UPDATE(b)

    PRINT 'FIRING'

GO

  

INSERT junk (a, b)
VALUES (1, 2)		 /*IF UPDATE is true for both columns, */
/*the trigger is activated. */
UPDATE junk
SET a = 1, b = 2		 /*IF UPDATE is true for both columns, */
/*the trigger is activated. */
INSERT junk
VALUES (NULL, 2)		 /*Explicit NULL. */
/*IF UPDATE is true for both columns, */
/*the trigger is activated. */
UPDATE junk
SET a = NULL, b = 2		 /*IF UPDATE is true for both columns, */
/*the trigger is activated. */
INSERT junk (b)
VALUES (2)		 /*No default on column a. */
/*IF UPDATE is true for both columns, */
/*the trigger is activated. */
UPDATE junk
SET b = 2		 /*IF UPDATE is not true for both columns, */
/*the trigger is not activated. */
INSERT junk (a)
VALUES (2)		 /*Default on column b. */
/*IF UPDATE is true for both columns, */
/*the trigger is activated. */
UPDATE junk
SET a = 2		 /*IF UPDATE is not true for both columns, */
/*the trigger is not activated. */
INSERT junk (a, b)
VALUES (2, DEFAULT)		 /*DEFAULT explicitly inserted. */
/*IF UPDATE is true for both columns, */
/*the trigger is activated. */
UPDATE junk
SET a = 2, b = DEFAULT		 /*IF UPDATE is true for both columns, */
/*the trigger is activated. */

See Also
Null Values DEFAULT Definitions

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.