CREATE TRIGGER trigger_name

     ON table_name

     [WITH ENCRYPTION]

     FOR [DELETE, INSERT, UPDATE]

     AS

       T-SQL語(yǔ)句

     GO

--GradeInfo表中插入一條數(shù)據(jù)，MyStudentInfo表中插入一條記錄
IF (object_id('tr_insert','tr') is not null)
    drop trigger tr_insert
GO
CREATE trigger tr_insert
on GradeInfo
after insert --插入觸發(fā)
as
 begin
   --定義變量
   declare @GradeId int
   --在inserted表中查詢已經(jīng)插入記錄信息
   select @GradeId=id from INSERTED
   --MyStudentInfo表中插入數(shù)據(jù)
   insert INTO MyStudentInfo (GradeId) VALUES (@GradeId)
   print '插入成功!'
 end

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

select * from MyStudentInfo where GradeId=11

--刪除MyStudentInfo表中的數(shù)據(jù)，插入備份表
 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 '正在備份數(shù)據(jù)......'
 IF (object_id('MyStudentInfo_Back','U') is not null)
 --存在表，直接插入數(shù)據(jù)
   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
   --聲明變量，存儲(chǔ)更新前和更新后的姓名
   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='張三測(cè)試' where Id=1

 --update更新列級(jí)觸發(fā)器
   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 '系統(tǒng)提示：主鍵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

--創(chuàng)建instead of觸發(fā)器
/*MyStudentInfo表里面插入數(shù)據(jù)之前，先判斷GradeInfo表中是否有對(duì)應(yīng)的班級(jí)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 '該班級(jí)存在，可以插入'
  else
    begin
      print '該班級(jí)不存在，不可以插入'
   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 '別想著刪庫(kù)！好好打你的代碼'
?? 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