觸發器詳解
更新時間：2022年02月24日 14:21:15 作者：.NET開發菜鳥
本文詳細講解了中觸發器的用法sql 觸發器 在插入前 改變值，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
目錄
觸發器是一種特殊的存儲過程，觸發器主要是通過事件進行觸發而被自動調用執行，而存儲過程必須通過存儲過程的名稱被調用。
一、觸發器的定義
觸發器是在對表進行插入、更新或刪除操作時自動執行的特殊存儲過程。觸發器通常用于強制業務規則，觸發器是一種高級約束，可以定義比CHECK約束更為復雜的約束：可以執行復雜的SQL語句（if/while/case）,可以引用其他表中的列。觸發器定義在特定的表上，與表相關，自動觸發執行，不能直接調用，是一個事務（可回滾）。
二、觸發器分類
SQL 中觸發器可以分為兩類：DML觸發器和DDL觸發器，DML觸發器針對表，DDL觸發器會影響多種數據定義語言語句而觸發，這些語句有、alter、drop語句。
DML觸發器分為：
1、after觸發器（之后觸發）
2、 of觸發器（之前觸發）
after觸發器要求只有執行某一操作（、、）之后觸發器才能被觸發sql 觸發器 在插入前 改變值，且只能定義在表上。而 of觸發器表示并不執行其定義的操作（、、）而僅是執行觸發器本身，其優先級高于觸發語句的執行。
觸發器有兩個特殊的表：插入表（表）和刪除表(表)
這兩張表是邏輯表也是虛表，觸發器觸發時系統自動在內存中創建這兩張表，不會存儲在數據庫中。這兩張表都是只讀的，不允許修改。這兩張表的結果總是與被觸發器應用的表的結構相同。當觸發器完成工作后，這兩張表就會被刪除。表臨時保存了插入或更新后的記錄行，可以從表中檢查插入的數據是否滿足業務需求，如果不滿足，則向用戶報告錯誤消息，并回滾插入操作。表臨時保存了刪除或更新前的記錄行，可以從表中檢查被刪除的數據是否滿足業務需求，如果不滿足，則向用戶報告錯誤消息，并回滾刪除操作。數據的時候是先刪除表記錄，然后插入一條記錄，在和表就都有后的數據記錄了。
表和表存放的信息：
修改操作
表
表
增加()記錄
存放新增的記錄
無
刪除()記錄
無
存放被刪除的記錄
修改()記錄
存放更新后的記錄
存放更新前的記錄
三、創建觸發器
語法：
CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR [DELETE, INSERT, UPDATE]
AS
T-SQL語句
GO
WITH 表示加密觸發器定義的SQL文本
, , 指定觸發器的類型
1、創建類型的觸發器
插入觸發器
--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
2、觸發器
刪除表中的數據，插入備份表
--刪除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
刪除id=9的數據
delete FROM MyStudentInfo where Id=9
查詢備份表數據
select * from MyStudentInfo_Back
3、觸發器
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
更新id列
update GradeInfo SET Id=15 where Id=4
4、 of觸發器
of觸發器表示并不執行其定義的操作（、、）而僅是執行觸發器本身的內容，其優先級高于定義的SQL語句的執行
語法：
create trigger tgr_name
on table_name
with encryption
instead of update...
as
begin
T-SQL
end
創建 of觸發器
--創建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
測試1，插入不存在的班級id
insert INTO MyStudentInfo (GradeId) VALUES (15)
測試2，插入存在的班級id
insert INTO MyStudentInfo (GradeId) VALUES (5)
DDL觸發器
? 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)
禁用DML觸發器
disable trigger tr_insteadOf on MyStudentInfo
啟用DML觸發器
enable trigger tr_insteadOf on MyStudentInfo
禁用DDL觸發器
disable trigger tr_DDL on database
啟用DDL觸發器
enable trigger tr_DDL on database
到此這篇關于SQL 觸發器詳解的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持腳本之家。