SQLServer觸發(fā)器

觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，只有當(dāng)試圖用數(shù)據(jù)操作語(yǔ)言DML來(lái)修改數(shù)據(jù)時(shí)才會(huì)觸發(fā)，DML包含對(duì)視圖和表的增、刪、改。

觸發(fā)器分為DML觸發(fā)器和DDL觸發(fā)器，其中DML觸發(fā)器會(huì)對(duì)數(shù)據(jù)表，視圖進(jìn)行insert、update、delete的時(shí)候觸發(fā)。DDL則是create、alter、drop等時(shí)觸發(fā)。

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

如果在創(chuàng)建觸發(fā)器的時(shí)候不指定類型，則默認(rèn)是after觸發(fā)器。

觸發(fā)器有兩個(gè)虛擬的臨時(shí)表：插入表（inserted）和刪除表（deleted），由系統(tǒng)在內(nèi)存中創(chuàng)建，不會(huì)保存到數(shù)據(jù)庫(kù)。并且兩張表都是只讀的。當(dāng)觸發(fā)器完成工作后，這兩張表會(huì)自動(dòng)刪除。

增加：inserted表 記錄

刪除：deleted表 記錄

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

修改數(shù)據(jù)時(shí)現(xiàn)刪掉該記錄，然后再增加一天新的記錄。這樣inserted和updated就都存在記錄了。

觸發(fā)器本身就是一個(gè)事務(wù)，可以利用事務(wù)的回滾，撤銷操作。事務(wù)就是要么都執(zhí)行，要么一條都不執(zhí)行。

創(chuàng)建觸發(fā)器

語(yǔ)法：

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

創(chuàng)建insert觸發(fā)器

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

對(duì)student表創(chuàng)建觸發(fā)器后對(duì)該表執(zhí)行insert后

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

查看運(yùn)行結(jié)果

創(chuàng)建update觸發(fā)器

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; --查找更新前的數(shù)據(jù)    select @newName = name from inserted;--查找更新后的數(shù)據(jù)    if(@oldName = @newName)    begin        print '數(shù)據(jù)相同'        rollback tran;   --回滾 不執(zhí)行修改操作    end    else        print '修改成功'

update student set name='海盜船長(zhǎng)' 

事務(wù)在觸發(fā)器中結(jié)束。批處理已中止。

update student set name='粉紅娘娘'

修改成功

創(chuàng)建delete觸發(fā)器

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 '不存在，創(chuàng)建再插入'        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

創(chuàng)建instead of觸發(fā)器

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 先觸發(fā)    --先刪除user表信息    delete from [user] where userName = @name;    --再刪除student表信息    delete from [student] where id = @id;delete from student where name = '海盜船長(zhǎng)'

修改觸發(fā)器

alter trigger tgr_test_inserton studentfor insertasprint 'ok'

禁用觸發(fā)器

disable trigger tgr_test_insert on student

啟用觸發(fā)器

enable trigger tgr_test_insert on student

查詢已創(chuàng)建的觸發(fā)器

select * from sys.triggers