首页 > 代码库 > SqlCollections - 触发器
SqlCollections - 触发器
1 --==============================触发器=============================== 2 --分类及注意 3 --触发器分为DML触发器和DDL触发器,DML触发器又分After触发器和Instead Of触发器 4 --After触发器只可以建在表上,Instead Of触发器可以建在表或视图上 5 --每个触发器都定义了插入表Inserted和删除表Deleted,存在于服务器内存中,不是物理表 6 --Truncate Table语句不会激发Delete类型触发器 7 8 --After触发器 9 --例:插入一条记录时,显示友好提示 10 CREATE TRIGGER 产品_Insert1 11 ON 产品 --ON+表名或视图名(是有Instead Of触发器可以建在视图上) 12 AFTER INSERT 13 AS 14 BEGIN 15 PRINT ‘又添加了一种产品(1)!‘ 16 END 17 GO 18 --再创建一个 19 CREATE TRIGGER 产品_Insert2 20 ON 产品 21 AFTER INSERT 22 AS 23 BEGIN 24 PRINT ‘又添加了一种产品(2)!‘ 25 END 26 GO 27 /*显示:又添加了一种产品(1)! 28 又添加了一种产品(2)!*/ 29 INSERT INTO 产品(产品名称) VALUES (‘小苹果‘) 30 31 --After触发器的激活顺序 32 --参数1:触发器名,注意引号; 33 --参数2:激活次序,包括First,Last,None,None代表不指定; 34 --参数3:激活动作,可以为Insert,Update或Delete 35 EXEC sp_settriggerorder ‘产品_Insert1‘,‘Last‘,‘Insert‘ 36 GO 37 EXEC sp_settriggerorder ‘产品_Insert2‘,‘First‘,‘Insert‘ 38 GO 39 /*显示:又添加了一种产品(2)! 40 又添加了一种产品(1)! 41 顺序已经反了*/ 42 INSERT INTO 产品(产品名称) VALUES (‘小苹果‘) 43 44 --在After触发器中回滚 45 --例:插入记录时,若折扣大于0.6,则执行回滚不插入 46 CREATE TRIGGER 订单明细_Insert 47 ON 订单明细 48 AFTER INSERT 49 AS 50 BEGIN 51 IF(SELECT 折扣 FROM INSERTED) > 0.6 --INSERTED:存放新纪录的表,DELETED:存放旧记录的表 52 BEGIN 53 PRINT ‘折扣不允许大于0.6!‘ 54 ROLLBACK TRAN --回滚操作 55 END 56 END 57 GO 58 INSERT INTO 订单明细(订单ID,产品ID,单价,数量,折扣) 59 VALUES(11077,1,18,1,0.8) 60 GO 61 --数据被回滚,未成功插入 62 SELECT * FROM 订单明细 WHERE 订单ID = 11077 AND 折扣=0.8 63 64 --Instead Of触发器 65 --上一例用Instead Of触发器更好,在插入前先判断,减少服务器负担 66 CREATE TRIGGER 订单明细_Insert2 67 ON 订单明细 68 INSTEAD OF INSERT 69 AS 70 BEGIN 71 DECLARE @订单ID int, 72 @产品ID int, 73 @单价 money, 74 @数量 smallint, 75 @折扣 real; 76 SELECT @订单ID = 订单ID FROM inserted 77 SELECT @产品ID = 产品ID FROM inserted 78 SELECT @单价 = 单价 FROM inserted 79 SELECT @数量 = 数量 FROM inserted 80 SELECT @折扣 = 折扣 FROM inserted 81 IF(@折扣 > 0.6) 82 BEGIN 83 PRINT ‘折扣不允许大于0.6!‘ 84 END 85 ELSE 86 BEGIN 87 INSERT INTO 订单明细(订单ID,产品ID,单价,数量,折扣) 88 VALUES(@订单ID,@产品ID,@单价,@数量,@折扣) 89 END 90 END 91 GO 92 INSERT INTO 订单明细(订单ID,产品ID,单价,数量,折扣) 93 VALUES(11077,1,18,1,0.9) 94 GO 95 --数据被回滚,未成功插入 96 SELECT * FROM 订单明细 WHERE 订单ID = 11077 AND 折扣=0.9 97 98 --查看指定触发器信息 99 EXEC sp_help ‘订单明细_Insert‘ --简要信息100 EXEC sp_helptext ‘订单明细_Insert‘ --具体脚本101 102 --重命名触发器103 EXEC sp_rename ‘订单明细_Insert‘,‘订单明细_Insert1‘104 105 --删除触发器106 --删除表时会自动删除该表的触发器107 DROP TRIGGER 订单明细_Insert1 --注意不能带引号108 109 --启用与禁用触发器110 ALTER TABLE 订单明细111 DISABLE TRIGGER 订单明细_Insert2 --禁用112 --ENABLE TRIGGER 订单明细_Insert2 --启用113 --ENABLE TRIGGER ALL --启用所有114 115 --DDL触发器116 --DDL触发器只能定义FOR或AFTER(意义一样),不能定义Instead Of117 CREATE TRIGGER 禁止修改或删除该数据库的表118 ON DATABASE --ON后面可跟DATABASE,作用到当前数据库,或跟ALL SERVER,作用到当前服务器的所有数据库119 FOR ALTER_TABLE,DROP_TABLE --当作用在ALL SERVER时,可以FOR DROP_DATABASE等120 AS121 BEGIN122 PRINT ‘不允许操作数据表!‘123 ROLLBACK TRAN124 END125 GO126 DROP TABLE tbl1127 128 --创建自定义数据库日志129 --创建日志记录表130 CREATE TABLE 日志记录表131 (132 编号 int IDENTITY(1,1) PRIMARY KEY NOT NULL,133 事件 varchar(5000) NULL,134 所用语句 varchar(5000) NULL,135 操作者 varchar(50) NULL,136 操作时间 datetime NULL137 )138 GO139 --创建DDL触发器140 CREATE TRIGGER 记录日志141 ON DATABASE142 FOR DDL_DATABASE_LEVEL_EVENTS143 AS144 BEGIN145 DECLARE @log XML146 DECLARE @event varchar(5000)147 DECLARE @sql varchar(5000)148 DECLARE @operator varchar(50)149 SET @log = EVENTDATA()150 SET @event = @log.value(‘(EVENT_INSTANCE/EventType)[1]‘,‘nvarchar(100)‘)151 SET @sql = @log.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘,‘nvarchar(2000)‘)152 SET @operator = CONVERT(varchar(50),CURRENT_USER)153 --插入日志表154 INSERT INTO 日志记录表(事件,所用语句,操作者,操作时间)155 VALUES(@event,@sql,@operator,GETDATE())156 END 157 GO158 --验证日志159 CREATE TABLE 测试日志表(编号 int PRIMARY KEY,测试内容 varchar(50))160 GO161 --先把之前的DDL触发器禁用掉162 DISABLE TRIGGER 禁止修改或删除该数据库的表 ON DATABASE --禁用DDL触发器的写法163 GO164 --删除表165 DROP TABLE 测试日志表166 GO167 --查看日志168 SELECT * FROM 日志记录表169 170 --判断字段是否被更改171 CREATE TRIGGER 看折扣是否被更改172 ON 订单明细173 AFTER UPDATE174 AS175 BEGIN176 IF UPDATE(折扣) --该字段是否被更新177 BEGIN178 PRINT ‘折扣字段将被修改‘179 END180 ELSE181 BEGIN182 PRINT ‘折扣字段没有被修改‘183 END184 END185 GO186 --输出:折扣字段将被修改187 UPDATE 订单明细 SET 折扣 = 0.2 WHERE 订单ID = 1 AND 产品ID = 1188 --输出:折扣字段没有被修改189 UPDATE 订单明细 SET 数量 = 1 WHERE 订单ID = 1 AND 产品ID = 1190 --输出:折扣字段将被修改191 UPDATE 订单明细 SET 折扣 = 0.2,数量 = 1 WHERE 订单ID = 1 AND 产品ID = 1
SqlCollections - 触发器
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。