SQLSERVER 中的触发器
你可以将触发器理解成现实生活中开关, 触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活
它的分类,sqlserver 中有两类触发器,DML触发器和DDL触发器。触发器可以用来实现对表实施复杂的完整性约束,触发器可通过数据库中的相关表实现级联 更改,可以强制
比用CHECK约束定义的约束更为复杂的约束。与 CHECK 约束不同,触发器可以引用其它表中的列,例如触发器可以使用另一个表中的 SELECT 比较
插入或更新的数据,以及执行其它操作。触发器也可以根据数据修改前后的表状态,再行采取对策。一个表中的多个同类触发器(INSERT、UPDATE 或
DELETE)允许采取多个不同的对策以响应同一个修改语句。
1.DML 触发器
DML(数据操纵语言 DATA MANIPULATION LANGUAGE) 触发器:是指在数据库中发生DML事件时将启用,DML事件即是指在表或视图中修改数据的insert,update,delete语句。
2.DDL 触发器
DDL(数据定义语言 DATA DEFINITION LANGUAGE)触发器:是指当服务器或数据库发生(DDL事件时将启用,DDL事件包括表或者索引中create,alter,drop)
登录触发器:是指当用户登录sql server 实例建立会话时触发。
3. 理解两个逻辑表(INSERTED,DELETED)
INSERTED:要插入(inserted)到数据库中数据的一个副本,保存到inserted中
DELETED:保存源数据的一个副本到DELETED中,在数据被操作(update deleted)之前。
这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后﹐与该触发器相关的这两个表也被删除。
换一种方式来解释:
在delete数据的时候,可以假定数据库将要删除的数据放到一个deleted临时表中,我们可以向读取普通的表一样,select 字段 from deleted
而insert的时候道理一样,只不过是把要插入的数据放在inserted表中。
更新操作可以认为是执行了两个操作,先把那一行记录delete掉,然后再insert,这样update操作实际上就对deleted表和inserted表的操作,所以不会有updated表了。(ps:你可以在sqlserver 中写一些测试例子俩加上印象)
4.Instead of 触发器
1.Instead of触发器用于替代引起触发器执行的T-SQL语句。
2.Instead of 触发器也可以用于视图﹐用来扩展视图可以支持的更新操作。
3. 数据表里的数据禁止修改 。
4. 有可能要回滚修改的SQL语句,有些判断需要在执行之前进行,所以After触发器并不是最好的选择,Instead Of触发器更合适。
5.在试图中使用触发器, After触发器不能在视图中使用。
6.Instead of 触发器在约束之前执行﹐所以它可以对约束进行一些预处理。
5.After触发器
After触发器在一个Insert,Update或Deleted语句之后执行,
After触发器只能用于表,一个表的每个修改动作都可以有多个After触发器。
触发器的执行过程 如果一个Insert﹑update或者delete语句违反了约束﹐那幺After触发器不会执行﹐因为对约束的检查是在After触发器被激动之前发生的。所以After触发器不能超越约束。
SQL Server创建的默认的触发器为AFTER触发器
他们的顺序应该是这样的:
5.基本语法
create trigger trigger_name on {table_name | view_name} {for | After | Instead of } [ insert, update,delete ] as sql_statement
--修改 -- 把CREATE 改成ALTER --查看触发器中的语句; EXEC SP_HELPTEXT TRIGGER_NAME --查看数据库中触发器; SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' --删除触发器 DROP TRIGGER TRIGGER_NAME --启用或关闭数据库中触发器; DISABLE TRIGGER[NAME] ON DATABASE ENABLE TRIGGER[NAME] ON DATABASE
6.我们还是看看具体的实例吧
应用场景一 完整性约束(修改,删除) --after触发器
--如果更改学生学号,record 表中的学生也同时改变
--在关系型数据中,未保持数据的完整和一致性,可能会用主外键来约束
--在NOSQL,我这里就用触发器;
--测试数据库 CREATE TABLE STUDENT( STUDENTID VARCHAR(20) PRIMARY KEY , NAME VARCHAR(100) ) GO CREATE TABLE RECORD( ID INT PRIMARY KEY IDENTITY(1,1), BOOKNAME VARCHAR(40), STUDENTID VARCHAR(20) ) GO --测试数据 INSERT STUDENT VALUES('110','JACK') INSERT STUDENT VALUES('111','TOM') INSERT RECORD VALUES('傲慢与偏见','110') INSERT RECORD VALUES('老人与海','110') INSERT RECORD VALUES('我的童年','110') --如果更改学生学号,record 表中的学生也同时改变 GO ALTER TRIGGER TRG ON STUDENT FOR UPDATE AS IF UPDATE(STUDENTID) BEGIN DECLARE @OLDID VARCHAR(20), @NEWID VARCHAR(20) SELECT @OLDID=STUDENTID FROM DELETED SELECT @NEWID=STUDENTID FROM INSERTED UPDATE RECORD SET STUDENTID=@NEWID WHERE STUDENTID=@OLDID END SELECT * FROM STUDENT GO UPDATE STUDENT SET STUDENTID='110_1' WHERE STUDENTID='110' SELECT *FROM RECORD --如果 删除学生号,同时删除它的借书记录
应用场景二 日志记录 --after触发器
如果修改学生的命名,这个操作被记录到stulog 表中
--测试数据库 CREATE TABLE STUDENT( ID INT PRIMARY KEY IDENTITY(1,1), NAME VARCHAR(40) NULL, age INT NULL, Sex VARCHAR(2) NULL ) GO CREATE TABLE STULOG ( ID INT PRIMARY KEY IDENTITY(1,1), LOGCONTENT VARCHAR(200), TIMES DATETIME ) GO --触发器建立 CREATE TRIGGER TRG ON STUDENT FOR UPDATE AS IF UPDATE(NAME) --某一特定的字段 BEGIN DECLARE @OLDNAME VARCHAR(30), @NEWNAME VARCHAR(30), @CONTENT VARCHAR(100) SELECT @OLDNAME=NAME FROM DELETED SELECT @NEWNAME=NAME FROM INSERTED SET @CONTENT=@OLDNAME+' HAS CHANGE TO '+@NEWNAME INSERT STULOG VALUES(@CONTENT,GETDATE()) END GO --测试 UPDATE STUDENT SET NAME='JACK' WHERE ID=4 SELECT * FROM STUDENT SELECT * FROM STULOG --结果 -- 4 JACK 24 男 -- 1 XX HAS CHANGE TO JACK 2015-09-25 13:49:50.110
应用场景三 避免重复插入 --instead of触发器
当添加新商品到购物车中时,检查物品是否已经存在,如果已经存在就修改数量,不存在就添加
--测试数据库 CREATE TABLE SHOPPINGCAR( ID INT NOT NULL PRIMARY KEY IDENTITY(1,1), ITEMID VARCHAR(100), AMOUNT INT , REMARK VARCHAR(100) ) GO CREATE TRIGGER ISHAVE ON SHOPPINGCAR INSTEAD OF INSERT --这里要用instead of 触发器 AS DECLARE @ITEMID VARCHAR(50), @AMOUNT INT, @REMARK VARCHAR(100) SELECT @ITEMID=ITEMID,@AMOUNT=AMOUNT,@REMARK=REMARK FROM INSERTED IF EXISTS(SELECT ID FROM SHOPPINGCAR WHERE ITEMID=@ITEMID) BEGIN UPDATE SHOPPINGCAR SET AMOUNT=AMOUNT+@AMOUNT END ELSE BEGIN INSERT SHOPPINGCAR VALUES(@ITEMID,@AMOUNT,@REMARK) END GO --测试数据 INSERT SHOPPINGCAR VALUES('编号001商品',1,'GOOD') SELECT * FROM SHOPPINGCAR INSERT SHOPPINGCAR VALUES('编号001商品',2,'GOOD') SELECT * FROM SHOPPINGCAR -- 测试结果: --1 编号001商品 3 GOOD
应用场景四
--订单和库存的关系,
--在向订单中添加商品信息时,先检查库存中是否有货,
--当添加到订单中后,库存的商品数量要相应的减少
整理中.......