首页 > 代码库 > 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,订单明细_Insert1104 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 - 触发器