一、概述

觸發器(TRIGGER)是一種特殊型別的存盤程序，包含了一組T-SQL陳述句，它的執行不是由程式呼叫，也不是手工啟動，而是由事件觸發的，在指定的表中的資料發生變化時自動生效，喚醒呼叫觸發器以回應 INSERT、UPDATE 或 DELETE 陳述句，無需再執行增刪改或存盤程序的操作，讓程式可以更加自動化，

二、型別

在這里插入圖片描述

1.DML觸發器

DML觸發器是在資料庫服務器中發生資料操作語言事件時執行的存盤程序，也就是說，DML觸發器在執行INSERT、DELETE、UPDATE陳述句時觸發，根據觸發的時機不同，DML觸發器又分為AFTER觸發器和INSTEAD OF觸發器兩種，
1.After 觸發器是在記錄已經修改完成后才會被激活執行，它主要用于記錄變更后的處理或檢查，一旦發現錯誤，也可以用 ROLLBACK TRANSACTION¹陳述句來回滾本次操作，(創建時使用for或after作為關鍵字）

比如我們最常見的就是當陳述句執行完成后，下面會自動彈出來的訊息欄:“命令已成功完成”，“警告：”等等，

2.INSTEAD OF 觸發器一般用于取代原本的操作，在記錄變更之前發生，它不去執行原來的SQL陳述句，而去執行觸發器中所定義的操作，(創建時使用instead of作為關鍵字）
3.幻表：系統為每個DML觸發器都定義了兩個特殊的表，當DML觸發器激發對INSERT，DELETE或UPDATE陳述句的回應時，這兩個特殊的表被創建: INSERTED表和DELETEED表，也稱為幻表(魔表)，
3.1 INSERTED表包含插入在觸發器表中的所有記錄的拷貝，或修改后的記錄值
3.2 DELETEED表包含了已從觸發器表中被洗掉的所有記錄，或修改前的記錄值

個人理解兩張表記錄的就是新，舊資料，兩張表之間是可以連接的，

2.DDL觸發器

DDL觸發器是在回應資料定義語言事件時執行的存盤程序，DDL觸發器一般用于執行資料庫中的管理任務，如審核和規范資料庫操作，防止對資料庫架構、視圖、表、存盤程序等進行的某些修改，
DDL觸發器并不回應INSERT、DELETE、UPDATE陳述句，它主要回應CREAT,ALTER,DROP,GRANT,DENY,REVOKE等陳述句，

DML和DDL的不同：
只有在完成T-SQL陳述句后才運行DDL觸發器，DDL觸發器無法作為替代觸發器使用；
DDL觸發器不會創建DELETEED表和INSERTED表，但是可以使用EVENTDATE函式捕獲有關資訊，

三、操作

1.創建觸發器

create trigger trigger_name
on { object name }
{ instead of | after | for }
{ [insert][,][update][,][delete] }
as
{ sql_statement [ …n] }

trigger_name 觸發器名稱(自取)
object name 觸發器針對的表(視圖)名稱
instead of | after | for 三選一
[insert][,][update][,][delete] 你希望做哪些事時會觸發機關

2.修改觸發器

類似于創建觸發器，只需要把上述陳述句中的 create 換成 alter 即可，

3.洗掉觸發器

drop trigger trigger_name

支持同時洗掉多個觸發器，英文逗號隔開，

4.查看觸發器

查看表中的觸發器
exec sp_helptrigger table_name

table_name是觸發器所在表的名稱，若不指定則列出所有的觸發器，

查看觸發器的定義版本
exec sp_helptext ‘trigger_name’
查看觸發器的所有者和創建時間
exec sp_help ‘trigger_name’

trigger_name 是觸發器的名稱

四、練習

1.資料庫的創建

create database 練習
go 
use 練習
go

create table student(
	sno char(10) primary key,
	sname char(20) not null,
	ssex char(2) check (Ssex in ('男','女')) not null,
	sage tinyint not null,
)
go 

insert student (sno, sname, ssex,sage) values('G2016001','李素素','女',19)
insert student (sno, sname, ssex,sage) values('G2016002','朱萍','女',21)
insert student (sno, sname, ssex,sage) values('G2016003','葉家裕','男',28)
insert student (sno, sname, ssex,sage) values('G2016004','鄧家如','女',20)
insert student (sno, sname, ssex,sage) values('G2016005','高曉','女',21)
insert student (sno, sname, ssex,sage) values('J2016001','楊華','男',20)
insert student (sno, sname, ssex,sage) values('J2016002','劉全珍','女',22)
insert student (sno, sname, ssex,sage) values('J2016003','王國','男',21)
insert student (sno, sname, ssex,sage) values('J2016004','孫榮','男',21)
insert student (sno, sname, ssex,sage) values('J2016005','胡娟','女',20)
insert student (sno, sname, ssex,sage) values('L2016001','張茂樺','男',22)
insert student (sno, sname, ssex,sage) values('L2016002','方杰','男',21)
insert student (sno, sname, ssex,sage) values('L2016003','劉可','女',20)
insert student (sno, sname, ssex,sage) values('L2016004','黃一秋','男',21)
insert student (sno, sname, ssex,sage) values('L2016005','唐治','男',20)
insert student (sno, sname, ssex,sage) values('L2016006','韓云','男',20)
insert student (sno, sname, ssex,sage) values('S2016001','徐川','男',20)
insert student (sno, sname, ssex,sage) values('S2016002','湯洪','男',21)
insert student (sno, sname, ssex,sage) values('S2016003','馬秋婷','女',20)
insert student (sno, sname, ssex,sage) values('S2016004','周英','女',20)
insert student (sno, sname, ssex,sage) values('S2016005','曹林','男',21)
go

create table course(
	cno char(10) primary key,
	cname char(30) not null,
	ccredit tinyint check (ccredit>=1 and ccredit<=10) not null,
	XKLB char(5) check (XKLB = '選修' OR [XKLB]='必修') not null
 )
go

insert course(cno, cname, ccredit,xklb) values ('B001','資料庫及應用',4,'必修')
insert course(cno, cname, ccredit,xklb) values ('B002','資料結構',4,'必修')
insert course(cno, cname, ccredit,xklb) values ('B003','程式設計思想',3,'必修')
insert course(cno, cname, ccredit,xklb) values ('B004','企業文化',3,'必修')
insert course(cno, cname, ccredit,xklb) values ('B005','會計學',3,'必修')
insert course(cno, cname, ccredit,xklb) values ('B006','高等數學',2,'必修')
insert course(cno, cname, ccredit,xklb) values ('B007','線性代數',3,'必修')
insert course(cno, cname, ccredit,xklb) values ('B008','大學英語',4,'必修')
insert course(cno, cname, ccredit,xklb) values ('B009','馬克思主義哲學',4,'必修')
insert course(cno, cname, ccredit,xklb) values ('X001','大學生就業指導',1,'選修')
insert course(cno, cname, ccredit,xklb) values ('X002','大學生心理指導',1,'選修')
insert course(cno, cname, ccredit,xklb) values ('X003','大學生思想修養',1,'選修')
go

create table sc(
	sno char(10) not null,
	cno char(10) not null,
	grade numeric(3, 0)check (grade>=0 and grade<=100),
	primary key(sno,cno),
	foreign key(sno)references student(sno),
	foreign key(cno)references course(cno)
)
go

insert sc (sno,cno,grade) values ('G2016001','B004',95)
insert sc (sno,cno,grade) values ('G2016001','B008',88)
insert sc (sno,cno,grade) values ('J2016001','B001',90)
insert sc (sno,cno,grade) values ('J2016003','B001',85)
insert sc (sno,cno,grade) values ('J2016005','B003',87)
insert sc (sno,cno,grade) values ('J2016001','B002',91)
insert sc (sno,cno,grade) values ('J2016004','B008',50)
insert sc (sno,cno,grade) values ('J2016004','B002',85)
insert sc (sno,cno,grade) values ('G2016003','B005',86)
insert sc (sno,cno,grade) values ('G2016003','X001',91)
insert sc (sno,cno,grade) values ('L2016001','B007',80)
insert sc (sno,cno,grade) values ('L2016002','B007',92)
insert sc (sno,cno,grade) values ('L2016004','B007',83)
insert sc (sno,cno,grade) values ('L2016005','B006',78)
insert sc (sno,cno,grade) values ('L2016001','B006',48)
insert sc (sno,cno,grade) values ('L2016006','B008',90)
insert sc (sno,cno,grade) values ('S2016001','B008',85)
insert sc (sno,cno,grade) values ('S2016002','B008',79)
insert sc (sno,cno,grade) values ('S2016003','B008',80)
insert sc (sno,cno,grade) values ('S2016001','B006',90)
insert sc (sno,cno,grade) values ('S2016004','B006',86)
insert sc (sno,cno,grade) values ('S2016005','X003',92)
insert sc (sno,cno,grade) values ('S2016003','X003',88)
insert sc (sno,cno,grade) values ('G2016002','X002',NULL)
insert sc (sno,cno,grade) values ('G2016005','X003',NULL)
go

2.練習題

最好在每次前面加一段陳述句，非必須，看自己，

--在目標資料庫中進行操作
use database_name
go
-- 判斷名為 trigger_name的觸發器是否存在
if exists(select * from sys.triggers where name='trigger_name')
--洗掉名為 trigger_name的觸發器
drop trigger trigger_DDL_Table on database  
go

在COURSE表上創建一個名為“AfterInsertTRI”的INSERT后觸發器，判斷插入的課程學分是否大于5，如果大于5，將其學分修改為5，

create trigger AfterInsertTRI on Course
after insert 
as
update Course
set Ccredit = 5 where Ccredit in (
	select Ccredit from Course where Ccredit > 5
)

洗掉上題創建的“AfterInsertTRI”觸發器，然后在COURSE表上創建一個名為“InsteadInsertTRI”的INSERT替代觸發器，觸發器判斷插入的課程學分是否大于5，如果大于5，將學分修改為5再插入；如果學分小于等于5，按原插入資料插入

--這是用的后觸發器，我寫錯了，沒讀題...
drop trigger InsteadInsertTRI
go--注意這個go，批處理結束后是必須要有的
create trigger InsteadInsertTRI on Course
for insert,update
as
update Course
set Ccredit = 5
from Course,inserted
where Course.Cno = inserted.Cno and inserted.Ccredit > 5

drop trigger AfterInsertTRI
go
create TRIGGER InsteadInsertTRI ON course
instead of INSERT 
as 
begin
    insert  course(cno,cname,ccredit,xklb)
    select cno,cname,ccredit,xklb from inserted
    update course set ccredit = 5 where ccredit > 5
end

使用UPDATE觸發器實作：當修改SC表的成績時（每次只更新一行資料），如果修改后的成績小于等于修改以前的，則不允許修改，并提示“修改后成績小于等于修改前成績，修改失敗！”，否則提示“修改成功！”，

create trigger UPDATETRG on sc
for update 
as
if update(grade) 
begin
	if  exists(select * from inserted join deleted on inserted.sno=deleted.sno 
where inserted.grade < deleted.grade)
	begin
		print '修改后成績小于等于修改前成績，修改失敗！'
		rollback transaction
	end
	else
		print'修改成功！'
end

rollback
回滾，類似于撤銷， ??

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/240046.html

標籤：其他

上一篇：好隊友paas--如何上傳excel到好隊友中生成資料表

下一篇：萬能BaseMapper，BaseService，BaseController模板（偷懶專用）