怎样用隐性和显式空值激活触发器

上一篇 / 下一篇 2008-04-25 15:36:00 / 个人分类：致胜秘笺

在列中插入显式空值，或使用 DEFAULT 关键字为列赋值，都可以按预期激活触发器。同样，当没有在 INSERT 语句中为列指定值时，触发器仍可以在下列条件下激活：

◆由于不存在 DEFAULT 定义，列中插入了一个隐性空值。

◆由于 DEFAULT 定义确实存在，列中插入了一个默认值。

示例：用空值和默认值测试触发器激活

下列示例表明触发器如何受隐性和显式空值的影响。创建小型表以容纳两个触发器的值。一列包含空值，另一列包含默认值。触发器评价上述两列是否需要修改并且显示激活触发器时的信息。一系列 INSERT 语句通过插入隐性和显式空值的组合来测试触发器激活。

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

导入论坛引用链接收藏分享给好友管理举报

TAG: 触发器

查看全部评论