8、MSSQL触发器

定义：

两种主要机制来强制业务规则和数据完整性：约束和触发器。触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。唤醒调用触发器以响应 INSERT、UPDATE 或 DELETE 语句。触发器可以查询其它表，并可以包含复杂的Transact-SQL语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误（例如，磁盘空间不足），则整个事务即自动回滚。

语法

参考：https://www.cnblogs.com/liuqiyun/p/8603088.html https://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html

IF EXISTS(  SELECT * sysobjects WHERE id=OBJECT_ID("触发器名") and xtype = 'TR')
BEGIN
	DROP TRIGGER "触发器名"
END
GO
CREATE TRIGGER "触发器名" ON "表名"  FOR INSERT/UPDATE/DELETE
AS 
BEGIN
   --可以判断某列是否更新
   --IF UPDATE(列名)
   -- BEGIN
   --END
END
GO

模板

(FOR UPDATE,DELETE,INSERT)允许多个操作符，逗号隔开。WITH ENCRYPTION：放在on 表名之后，加密触发器

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id=OBJECT_ID('触发器名') AND xtype = 'TR')
BEGIN
	DROP TRIGGER 触发器名
END
GO
CREATE TRIGGER 触发器名 ON 表名  FOR UPDATE/DELETE/INSERT
AS 
BEGIN
  BEGIN TRANSACTION
BEGIN TRY;
--sql语句
--插入操作（Insert）：Inserted表有数据，Deleted表无数据 
--删除操作（Delete）：Inserted表无数据，Deleted表有数据 
--更新操作（Update）：Inserted表有数据（新数据），Deleted表有数据（旧数据） 
IF EXISTS(SELECT  1 FROM  inserted) AND  NOT  EXISTS(SELECT 1 FROM deleted)
BEGIN 
--INSERT
END 
 
IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
BEGIN
--UPDATE
END
  
IF EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted)
BEGIN
--DELETE
END

COMMIT TRANSACTION
END TRY
BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;
		SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
		RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);		
 ROLLBACK TRANSACTION
END CATCH
END
GO

实际应用

附件：t_g_DeleteRdrecord11(备料单触发器).sql
测试：

CREATE TABLE [dbo].[Test](
	[name] [NCHAR](10) NULL,
	[age] [SMALLINT] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[TestTrigger](
	[name] [NCHAR](10) NULL,
	[age] [SMALLINT] NULL
) ON [PRIMARY]

触发器说明：如果删除Test表的数据，把删除的数据插入到TestTrigger表中

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id=OBJECT_ID('t_g_DeleteTableTest') AND xtype = 'TR')
BEGIN
	DROP TRIGGER t_g_DeleteTableTest
END
GO
CREATE TRIGGER t_g_DeleteTableTest ON Test  FOR DELETE
AS 
BEGIN
  BEGIN TRANSACTION
BEGIN TRY;
--sql语句
--插入操作（Insert）：Inserted表有数据，Deleted表无数据 
--删除操作（Delete）：Inserted表无数据，Deleted表有数据 
--更新操作（Update）：Inserted表有数据（新数据），Deleted表有数据（旧数据）  
IF EXISTS(SELECT 1 FROM Deleted) AND NOT EXISTS(SELECT 1 FROM Inserted)
BEGIN
	INSERT INTO dbo.TestTrigger( name, age )
	SELECT name, age FROM Deleted;
END

COMMIT TRANSACTION
END TRY
BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;
		SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
		RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);		
 ROLLBACK TRANSACTION
END CATCH
END
GO

结果：

触发器查看，修改执行顺序

利用系统视图
USE Test;
SELECT * FROM  sys.triggers

启用或禁用触发器

--禁用触发器
disable trigger tgr_message on student;
--启用触发器
enable trigger tgr_message on student;
--删除触发器
drop trigger triggerName

查询已存在的触发器

--查询已存在的触发器
select * from sys.triggers;
select * from sys.objects where type = 'TR';