SQLServer觸發器

觸發器是一種特殊的存儲過程，只有當試圖用數據操作語言DML來修改數據時才會觸發，DML包含對視圖和表的增、刪、改。

觸發器分為DML觸發器和DDL觸發器，其中DML觸發器會對數據表，視圖進行insert、update、delete的時候觸發。DDL則是create、alter、drop等時觸發。

DML觸發器又分為after（之后觸發）和insert of（之前觸發）觸發器。

如果在創建觸發器的時候不指定類型，則默認是after觸發器。

觸發器有兩個虛擬的臨時表：插入表（inserted）和刪除表（deleted），由系統在內存中創建，不會保存到數據庫。并且兩張表都是只讀的。當觸發器完成工作后，這兩張表會自動刪除。

增加：inserted表 記錄

刪除：deleted表 記錄

修改：inserted表記錄修改前 deleted表記錄修改后

修改數據時現刪掉該記錄，然后再增加一天新的記錄。這樣inserted和updated就都存在記錄了。

觸發器本身就是一個事務，可以利用事務的回滾，撤銷操作。事務就是要么都執行，要么一條都不執行。

創建觸發器

語法：

create trigger [shema_name . ] trg_nameon { table | view }[ with encryption ]{ for | after | instead of }{ insert , update , delete }assql_statement

創建insert觸發器

if(object_id('tgr_test_insert','tr') is not null)    drop trigger tgr_test_insert --刪除觸發器gocreate trigger tgr_test_inserton student --創建在student表    for insert --insert觸發asdeclare @id int,@name varchar(20),@sex char(2),@age int--查詢inserted表中已經插入的信息select @id = id,@name = name,@sex = sex, @age = age from inserted--用查到的信息加入到會員表中if(@age >30) --如果年齡不符合 則修改begin    update student set age = 30 where id = @id    PRint '年齡太大，已自動修改為30'endinsert into [user] (userName,passWord,RoleId) values (@name,@sex + cast(@age as varchar),3)print '添加學生成功！';print '添加會員成功！'

對student表創建觸發器后對該表執行insert后

insert into student (name,sex,age,mail) values ('海盜船長','男',88,'853020304@QQ.com')

查看運行結果

創建update觸發器

if(object_id('tgr_student_update','tr') is not null)    drop trigger tgr_student_updategocreate trigger tgr_student_updateon student    for updateas    declare @oldName varchar(10),@newName varchar(10)    select @oldName = name from deleted; --查找更新前的數據    select @newName = name from inserted;--查找更新后的數據    if(@oldName = @newName)    begin        print '數據相同'        rollback tran;   --回滾 不執行修改操作    end    else        print '修改成功'

update student set name='海盜船長' 

事務在觸發器中結束。批處理已中止。

update student set name='粉紅娘娘'

修改成功

創建delete觸發器

if(object_id('tgr_student_delete','tr')is not null)    drop trigger tgr_student_deletegocreate trigger tgr_student_deleteon student    for deleteas    if(exists(select * from sys.databases where name = 'studentBackup'))        insert into studentBackup select name,sex,age,mail from deleted;    else        print '不存在，創建再插入'        create table studentBackup        (            name varchar(20),            sex char(2),            age int,            mail varchar(20)        )        insert into studentBackup select name,sex,age,mail from deleted;

delete from studentselect * from studentselect * from studentBackup

創建instead of觸發器

if(object_id('tgr_student_inteadof','tr') is not null)    drop trigger tgr_student_inteadofgocreate trigger tgr_student_inteadofon student    instead of delete -- update , insertas    declare @id int,@name varchar(20)    select @id = id,@name = name from deleted;    --instead of 先觸發    --先刪除user表信息    delete from [user] where userName = @name;    --再刪除student表信息    delete from [student] where id = @id;delete from student where name = '海盜船長'

修改觸發器

alter trigger tgr_test_inserton studentfor insertasprint 'ok'

禁用觸發器

disable trigger tgr_test_insert on student

啟用觸發器

enable trigger tgr_test_insert on student

查詢已創建的觸發器

select * from sys.triggers