下文将为您详细解读隐性和显式空值激活sql server触发器的隐性方法,供您参考,和显活希望对您学习sql server触发器的式空使用能够有所启迪。
在列中插入显式空值,值激或使用 DEFAULT 关键字为列赋值,隐性都可以按预期激活触发器。和显活同样,式空当没有在 INSERT 语句中为列指定值时,值激sql server触发器仍可以在下列条件下激活:
◆由于不存在DEFAULT 定义,隐性列中插入了一个隐性空值。和显活
◆由于DEFAULT 定义确实存在,式空列中插入了一个默认值。
示例:用空值和默认值测试sql server触发器激活
下列示例表明sql server触发器如何受隐性和显式空值的影响。创建小型表以容纳两个触发器的值。一列包含空值,另一列包含默认值。触发器评价上述两列是否需要修改并且显示激活触发器时的信息。一系列 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
【编辑推荐】
教您如何查看Sql Server数据文件
查看sql server数据库连接数的三种方法
sql server数据库文件的压缩方法
sql server字符串的类型
sql server字符串截取实例分析
责任编辑:段燃 来源: 赛迪网 sql server触发器
(责任编辑:热点)
中国银行(03988.HK)拟赎回全部2.8亿股第二期境内优先股 每股面值人民币100元
英国脱欧后 欧盟的预算将出现200亿欧元的“窟窿” 需其余27个国家填补
帅丰电器(605336.SH)拟推176.25万股限制性股票激励计划 授予价格为13.62元/股
傲农生物(603363.SH):控股股东质押700万股 累计质押公司股份1.27亿股
谷歌与至少5家律所达成协议 帮助应对欧盟开出的24亿欧元罚单问题