sql server 触发器的使用

看数据库下的所有触发器及状态

SELECT a.name 数据表名 ,
sysobjects.name AS 触发器名 ,
sysobjects.crdate AS 创建时间 ,
sysobjects.info ,
sysobjects.status
FROM sysobjects
LEFT JOIN ( SELECT *
FROM sysobjects
WHERE xtype = 'U'
) AS a ON sysobjects.parent_obj = a.id
WHERE sysobjects.xtype = 'TR';

--type的含义
/*
C CHECK 约束
D 默认值或 DEFAULT 约束
F FOREIGN KEY 约束
L 日志
FN 标量函数
IF 内嵌表函数
P 存储过程
PK PRIMARY KEY 约束（类型是 K）
RF 复制筛选存储过程
S 系统表
TF 表函数
TR 触发器
U 用户表
UQ UNIQUE 约束（类型是 K）
V 视图
X 扩展存储过程
*/

--根据触发器名称查询触发器SQL如下

exec sp_helptext  触发器名称

--创建触发器语法

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_insert
GO
CREATE trigger tr_insert
on GradeInfo
after 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_name
on table_name
with 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_insteadOf
GO
CREATE trigger tr_insteadOf
on MyStudentInfo
instead of insert
as
  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 MyStudentInfo
alter column Name varchar(32)

删除触发器

drop trigger 触发器名称

禁用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