SQL-Instead of 觸發器

定義及優點

        INSTEAD OF觸發器指定執行觸發器而不是執行觸發 的SQL 陳述句，從而替代觸發陳述句的操作，
        在表或視圖上，每個 INSERT、UPDATE 或 DELETE 陳述句最多可以定義一個 INSTEAD OF 觸發器，并且，可以在每個具有 INSTEAD OF 觸發器的視圖上定義視圖，

優點：

?   使不能更新的視圖支持更新，包含多個基表的視圖必須使用 Instead Of 觸發器來支持參考表中資料的插入、更新和洗掉操作

?   拒絕批處理中的某些部分同時允許批處理的其它部分成功

?   如果遇到錯誤情況則采取備用操作

After 觸發器 與 Instead of 觸發器

# 理解

       對于 “ after觸發器只有執行某一操作insert、update、delete之后觸發器才被觸發，且只能定義在表上；instead of觸發器表示并不執行其定義的操作（insert、update、delete）而僅是執行觸發器本身，” 這句話我并不理解，

       但在無意間看到這一篇大佬的博客后（點擊這串文字，到達該博主博客），我真正理解了instead of 觸發器，

       after觸發器是在操作成功后，所采取的一些動作，而對于instead of觸發器，真正起作用的是觸發器里面的動作！

        用文字不好將其中的道理解釋清楚，所以請仔細理解以下得例子（例子也是來自那位大佬），

# 舉例

& 首先這里有兩個表，一個是新聞類別表（ caregory ），一個是新聞表（ news ），顧名思義，每一個新聞都有一個類別屬性，該屬性對應在類別表中；每一個類別可以對應有新聞表中的多條新聞（不知道解釋清楚沒），總的來說：news表的主鍵為id，外鍵為caID，與caregory的主鍵相對，此時通過主外鍵將兩個表連接起來，（建立好后，先在其中插入資料）

???  如下，創建了一個delete觸發器，其功能為洗掉一個新聞類別，以及該新聞類別下所有的新聞

1 CREATE TRIGGER De_c
2 ON caregory
3 FOR DELETE
4 AS
5     DELETE news WHERE caid = ( SELECT id FROM deleted )

創建好后，實作陳述句：DELETE FROM caregory WHERE id = 2;

會出現以下報錯：

?出錯原因：after觸發器是在執行完delete操作后執行的，執行操作后，該新聞類別已經不存在了，當觸發器再執行洗掉新聞時，新聞類別已經找不到了，就無法洗掉掉新聞（因為 news 表有外鍵，需要在外鍵存在的情況下才能洗掉新聞），這個時候 instead of 觸發器就能很好的解決這一問題，

???  如下，創建與delete 觸發器同樣功能的instead of 觸發器

1 CREATE TRIGGER De_in
2 ON caregory
3 INSTEAD OF DELETE
4 AS
5     DELETE news WHERE caid = ( SELECT id FROM deleted );
6     DELETE caregory WHERE id = (  SELECT id FROM deleted  );

創建好后，實作陳述句：DELETE FROM caregory WHERE id = 2;

這一次就能洗掉掉該新聞類別及其類別下得所有新聞，無報錯

?   到了這里，我想就可以理解到那句話的意思了

? after 觸發器（insert、update、delete觸發器）內的陳述句是在操作執行之后（已經作用在表上）才觸發執行的

? instead of 觸發器 并不會執行操作，那個操作仿佛就是一個觸發的命令，有了這個命令，instead of 觸發器觸發了，就會執行觸發器內的陳述句；若觸發器內只有像 raiserror 、print之類的不含操作性的陳述句，那該操作并不會真正的執行，但在觸發器內可以通過 inserted 或 deleted 表中獲取到本該執行該操作而形成的資料，

Instead of 觸發器 練習

 &練習一

以下有兩個表（個人表 - people ，教師表 - teacher ，每一個人有一個家教老師）：

# 1 ： 創建一個視圖，將學生與老師一一對應，直觀的表現其對應關系

1 CREATE VIEW v_teacher AS 
2 SELECT people.pid,people.pname,people.psex,teacher.tid,teacher.salary FROM people inner join teacher on people.pid = teacher.pid;
3 go

查看視圖如下：

# 2 ：在視圖上創建觸發器，以達到在多個表中插入行，

要求：若插入行中的個人資料出現重復時，將此資料插入到peoplerepeat表中；若教師資料出現重復，則將教師資料進行更新， 

觸發器如下（如有問題，歡迎指出）：

IF EXISTS ( SELECT 1 FROM sys.objects WHERE name = 'tr_in_vt' AND type = 'TR' )
    DROP TRIGGER tr_in_vt;
GO
CREATE TRIGGER tr_in_vt
ON v_teacher
INSTEAD OF INSERT
AS
    DECLARE @pid int,@pname varchar(20),@psex varchar(2),@tid int,@salary money;
    SELECT TOP 1 @pid = pid ,@pname = pname,@psex = psex,@tid = tid,@salary = salary FROM inserted
    IF EXISTS ( SELECT 1 FROM people WHERE pid = ( SELECT TOP 1 pid  FROM inserted) )
    BEGIN
        INSERT INTO peoplerepeat values (@pid,@pname,@psex,USER_NAME(),getdate());
        raiserror('插入的資料重復，已將重復的資料放入peoplerepeat表！',16,10);    
    END
    ELSE
    BEGIN
        INSERT INTO people values (@pid,@pname,@psex);
        IF EXISTS ( SELECT 1 FROM teacher WHERE tid = ( SELECT TOP 1 tid  FROM inserted) )
            BEGIN    
            UPDATE teacher SET pid = @pid,salary = @salary WHERE tid = @tid;
            raiserror('插入的資料重復，已將重復的資料更新入teacher表！',16,10);
            END    
        ELSE
            INSERT INTO people values (@tid,@pid,@salary);
    END

§ 對于簡單視圖，可以直接執行INSERT，UPDATE和DELETE操作；但是對于復雜視圖，不允許直接執行INSERT，UPDATE和DELETE操作，

   當視圖中包含以下結構之一，就表示為不可更新的視圖,都不允許直接執行DML操作

?具有集合運算子（UNION，UNION ALL，INTERSECT，MINUS）；

?具有分組函式（MIN,MAX,SUM,AVG,COUNT等）統計函式；

?具有GROUP BY，CONNECT BY或START WITH等子句,HAVING 子句；

?具有DISTINCT關鍵字；

?具有連接查詢（集合運算連接）；

?CASE 或者DECODE 陳述句

&練習二

以下有兩個表（員工表 emp ，部門表 dept）：

# 1 ：在視圖上創建觸發器，以達到在多個表中插入行，

要求：在插入員工資料時，對已經添加過的員工，不執行添加操作，其余不做限制，

觸發器如下（如有問題，歡迎指出）：

 1 CREATE TRIGGER v_i_tr
 2 on v_emp
 3 INSTEAD OF insert
 4 AS
 5 DECLARE @eid int,@did int,@ename varchar(20),@job varchar(20),@sal money,@dname varchar(20),@loc varchar(100);
 6 SELECT TOP 1 @eid = eid,@ename = ename,@job = job,@sal = sal,@dname = dname,@loc = loc,@did = did FROM inserted;
 7 IF not exists ( SELECT 1 FROM dept WHERE did = @did)
 8     INSERT INTO dept values (@did,@dname,@loc)
 9 IF exists ( SELECT 1 FROM emp WHERE eid = @eid)
10     RAISERROR('該員工已經添加過了，不用重復添加',16,10);
11 ELSE
12     INSERT INTO emp values (@eid,@ename,@job,@sal,@did);

# 2 ：在視圖上創建觸發器，以達到在多個表中更新行，

觸發器如下（如有問題，歡迎指出）：

 1 CREATE TRIGGER v_u_tr
 2 ON v_emp
 3 INSTEAD OF UPDATE
 4 AS
 5 DECLARE @eid int,@did int,@ename varchar(20),@job varchar(20),@sal money,@dname varchar(20),@loc varchar(100);
 6 SELECT TOP 1 @eid = eid,@ename = ename,@job = job,@sal = sal,@dname = dname,@loc = loc,@did = did FROM inserted;
 7 IF @did = (SELECT TOP 1 did FROM deleted)
 8     UPDATE dept SET dname = @dname,loc = @loc WHERE did = @did;
 9 ELSE IF not EXISTS (SELECT 1 FROM dept WHERE did = @did)
10     INSERT INTO dept values(@did,@dname,@loc);
11 UPDATE emp SET ename = @ename,job = @job,sal = @sal,did = @did WHERE eid = @eid;

# 3 ：在視圖上創建觸發器，以達到在多個表中洗掉行，

觸發器如下（如有問題，歡迎指出）：

 1 CREATE TRIGGER v_d_tr
 2 ON v_emp
 3 INSTEAD OF DELETE
 4 AS
 5 DECLARE @eid int,@did int;
 6 SELECT TOP 1 @eid = eid,@did = did FROM deleted;
 7 IF not EXISTS ( SELECT COUNT(1) FROM emp WHERE eid = @eid)
 8     RAISERROR('該員工不存在！無法洗掉',16,10);
 9 IF ( SELECT COUNT(1) FROM deleted ) > 1
10 BEGIN
11     print('洗掉了多條資料喲！');
12     DELETE emp WHERE did = @did;
13 END
14 ELSE
15     DELETE emp WHERE eid = @eid;

標籤：SQL Server

上一篇：TDSQL-A與CK的對比

下一篇：SQL 入門教程：創建視圖