SQL SERVER触发器怎么创建

本篇内容主要讲解"SQL SERVER触发器怎么创建"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"SQL SERVER触发器怎么创建"吧!

触发器是一种特殊的存储过程，触发器主要是通过事件进行触发而被自动调用执行，而存储过程必须通过存储过程的名称被调用。

一、触发器的定义

触发器是在对表进行插入、更新或删除操作时自动执行的特殊存储过程。触发器通常用于强制业务规则，触发器是一种高级约束，可以定义比CHECK约束更为复杂的约束：可以执行复杂的SQL语句（if/while/case）,可以引用其他表中的列。触发器定义在特定的表上，与表相关，自动触发执行，不能直接调用，是一个事务（可回滚）。

二、触发器分类

SQL SERVER中触发器可以分为两类：DML触发器和DDL触发器，DML触发器针对表，DDL触发器会影响多种数据定义语言语句而触发，这些语句有create、alter、drop语句。

DML触发器分为：

1、after触发器（之后触发）

• a、insert触发器

• b、update触发器

• c、delete触发器

2、instead of触发器（之前触发）

after触发器要求只有执行某一操作（insert、update、delete）之后触发器才能被触发，且只能定义在表上。而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身，其优先级高于触发语句的执行。

触发器有两个特殊的表：插入表（instered表）和删除表(deleted表)

这两张表是逻辑表也是虚表，触发器触发时系统自动在内存中创建这两张表，不会存储在数据库中。这两张表都是只读的，不允许修改。这两张表的结果总是与被触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。inserted表临时保存了插入或更新后的记录行，可以从inserted表中检查插入的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。deleted表临时保存了删除或更新前的记录行，可以从deleted表中检查被删除的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚删除操作。update数据的时候是先删除表记录，然后插入一条记录，在inserted和deleted表就都有update后的数据记录了。

inserted表和deleted表存放的信息：

三、创建触发器

语法：

     CREATE TRIGGER trigger_name     ON table_name     [WITH ENCRYPTION]     FOR [DELETE, INSERT, UPDATE]     AS       T-SQL语句     GO

WITH ENCRYPTION表示加密触发器定义的SQL文本

DELETE, INSERT, UPDATE指定触发器的类型

1、创建insert类型的触发器

插入触发器

--GradeInfo表中插入一条数据，MyStudentInfo表中插入一条记录IF (object_id('tr_insert','tr') is not null)    drop trigger tr_insertGOCREATE trigger tr_inserton GradeInfoafter insert --插入触发as begin   --定义变量   declare @GradeId int   --在inserted表中查询已经插入记录信息   select @GradeId=id from INSERTED   --MyStudentInfo表中插入数据   insert INTO MyStudentInfo (GradeId) VALUES (@GradeId)   print '插入成功!' end

插入数据

insert INTO GradeInfo VALUES (11,'C++')

查询数据

select * from MyStudentInfo where GradeId=11

2、delete触发器

删除MyStudentInfo表中的数据，插入备份表

--删除MyStudentInfo表中的数据，插入备份表 IF (object_id('tr_Delete','tr') is not null)    drop TRIGGER tr_Delete GO CREATE trigger tr_Delete on MyStudentInfo for delete as  begin    print '正在备份数据......' IF (object_id('MyStudentInfo_Back','U') is not null) --存在表，直接插入数据   insert INTO MyStudentInfo_Back SELECT * from DELETED else   select * into MyStudentInfo_Back from DELETED PRINT '备份完成'  end

删除前查询MyStudentInfo表数据

select * from MyStudentInfo

删除id=9的数据

delete FROM MyStudentInfo where Id=9

查询备份表数据

select * from MyStudentInfo_Back

3、update触发器

  IF (object_id('tr_Update','tr') is not null)     drop TRIGGER tr_Update  GO  CREATE trigger tr_Update  on MyStudentInfo  for update  as    begin   --声明变量，存储更新前和更新后的姓名   declare @OldName varchar(16),@NewName varchar(16)   select @OldName=name from DELETED   print '更新前姓名:'+@OldName   select @NewName=name from INSERTED   print '更新后姓名:'+@NewName end

把张三更新为"张三测试"

update MyStudentInfo SET Name='张三测试' where Id=1

update更新列级触发器

 --update更新列级触发器   IF (object_id('tr_update_column','tr') is not null)      drop TRIGGER tr_update_column   GO   CREATE trigger tr_update_column   on GradeInfo   for update   as     begin    IF(update(id))      begin     print '系统提示：主键ID不能更新'     rollback   end  end

更新id列

update GradeInfo SET Id=15 where Id=4

4、instead of触发器

instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身的内容，其优先级高于定义的SQL语句的执行

语法：

create trigger tgr_nameon table_namewith encryption    instead of update...as    begin  T-SQL end

创建instead of触发器

--创建instead of触发器/*MyStudentInfo表里面插入数据之前，先判断GradeInfo表中是否有对应的班级ID，如果没有，不允许插入，如果存在，则插入 */IF (object_id('tr_insteadOf','tr') is not null)   drop TRIGGER tr_insteadOfGOCREATE trigger tr_insteadOfon MyStudentInfoinstead of insertas  begin     IF exists(SELECT * FROM GradeInfo WHERE Id=(SELECT GradeId FROM INSERTED))      print '该班级存在，可以插入'  else    begin      print '该班级不存在，不可以插入'   rollback    end  end

测试1，插入不存在的班级id

insert INTO MyStudentInfo (GradeId) VALUES (15)

测试2，插入存在的班级id

insert INTO MyStudentInfo (GradeId) VALUES (5)

DDL触发器

  create trigger tr_DDL on database  for DROP_TABLE,ALTER_TABLE  as    begin      print '别想着删库！好好打你的代码'   rollback --回滚 end

测试删除表

drop TABLE MyStudentInfo

测试修改表结构

alter table MyStudentInfoalter column Name varchar(32)

禁用DML触发器

disable trigger tr_insteadOf on MyStudentInfo

启用DML触发器

enable trigger tr_insteadOf on MyStudentInfo

禁用DDL触发器

disable trigger tr_DDL on database

启用DDL触发器

enable trigger tr_DDL on database

到此，相信大家对"SQL SERVER触发器怎么创建"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！