SQL SERVER触发器详解

目录

• 一、触发器的定义
• 二、触发器分类
• 三、创建触发器
  • 1、创建insert类型的触发器
  • 2、delete触发器
  • 3、update触发器
  • 4、insteadof触发器
  • DDL触发器

     CREATE TRIGGER trigger_name

     ON table_name

     [WITH ENCRYPTION]

     FOR [DELETE, INSERT, UPDATE]

     AS

       T-SQL语句

     GO

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

--删除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

select * from MyStudentInfo

delete FROM MyStudentInfo where Id=9

select * from MyStudentInfo_Back

  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更新列级触发器
   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

update GradeInfo SET Id=15 where Id=4

create trigger tgr_name
on table_name
with encryption
    instead of update...
as
    begin
  T-SQL
 end

--创建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

insert INTO MyStudentInfo (GradeId) VALUES (15)

insert INTO MyStudentInfo (GradeId) VALUES (5)

  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)

disable trigger tr_insteadOf on MyStudentInfo

enable trigger tr_insteadOf on MyStudentInfo

disable trigger tr_DDL on database

enable trigger tr_DDL on database