觸發器遞歸的定義SQL

背景

A表UPDATE後，取B表某列再次UPDATE A表，這樣又觸發了A表的 UPDATE  觸發器，我的目的是隻觸發一次，是否設定nested triggers 選項關閉遞歸觸發器即可？

分析

首先，必須清楚觸發器遞歸的定義，觸發器有兩種遞歸方式：

1．     直接遞歸

A表上的觸發器更改（插入/删除/更新）A表資料，導緻A表的觸發器再次觸發，這種狀況稱之為直接遞歸；

2．     間接遞歸

A表上的觸發器更新B表資料，導緻觸發B表觸發器；而B表觸發器又更改A表資料，導緻A表觸發器再次觸發，這種狀況稱之為間接遞歸

解決方法—選項配置（影響所有範圍的觸發器）

SQL Server提供了資料庫級和伺服器級配置來确定遞歸觸發器是否被允許:

1.       伺服器級（使用存儲過程sp_configure 進行配置）

server trigger recursion 選項(SQL Server 2005)決定是否允許伺服器級觸發器直接遞歸激發；當此選項設定為1 (ON，預設值)時，将允許伺服器級觸發器遞歸激發；當設定為0 (OFF) 時，伺服器級觸發器不能遞歸激發。

nested triggers選項決定是否允許觸發器間接遞歸激發；當此選項設定為1 (ON，預設值)時，将允許觸發器遞歸激發；當設定為0 (OFF) 時，觸發器不能遞歸激發。

2.       資料庫級

RECURSIVE_TRIGGERS資料庫選項設定決定是否允許資料庫中的觸發器直接遞歸觸發；預設值為OFF，不允許直接遞歸觸發。

該選項可以通過存儲過程sp_dboption設定；對于SQL Server 2005，還可以使用類似下面的T-SQL設定：

ALTER DATABASE [DbName]

    SET RECURSIVE_TRIGGERS ON

使用選項決定遞歸觸發器的行為時，需要注意的是選項設定的有效範圍：

nested triggers選項決定所有的觸發器是否間接遞歸激發，這意味着這是一個SQL Server執行個體級的選項，設定将影響所有的觸發器。

server trigger recursion選項是SQL Server 2005中才有的（SQL Server 2005才有伺服器級觸發器）。

RECURSIVE_TRIGGERS選項影響配置它的資料庫中的所有觸發器。

其他解決方法（針對特定的觸發器）

如果隻希望特定的觸發器允許或者禁止觸發器，則SQL Server沒有選項可以做到；如果确實需要這樣的功能，可以在觸發器代碼中實作控制：

1．       使用update(列名)函數

此函數适用于對 UPDATE 的控制。對于"A表UPDATE 後，取B表某列再次UPDATE A表"，如果僅更新A表的某些列才觸發UPDATE B, 并且B 表再次UPDATE A表不會包含A表觸發UPDATE B的那些列,則在A表的觸發器中,使用IF UPDATE(列)來确定是否應該UPDATE B即可。

2．       使用@@NESTLEVEL

該變量值确定嵌套層數。

對于"A表update後，取B表某列再次UPDATE A表"，如果觸發者不是一個存儲過程, 則UPDATE A 的A表觸發器@@NESTLEVEL = 1, 到UPDATE B時, B表觸發器 @@NESTLEVEL = 2, B表觸發器再UPDATE A時, @@NESTLEVEL = 3。

是以如果 @@NESTLEVEL >=3 時, 一般表示遞歸了(當然, 前提是UPDATE A的觸發器本身沒有兩層的遞歸,即不能是存儲過程再調用存儲過程去UPDATE A。

3．       使用 @@PROCID

該全局變量傳回調用者的object_id。如果需要A表觸發B表觸發器，而B表觸發器再觸發A表觸發器時，A表觸發器不響應；則在A表觸發器中使用它來判斷觸發者是誰,如果是B表觸發器,則不處理就行了,類似下面這樣：

IF OBJECT_ID(N'B表觸發器名稱') = @@PROCID

BEGIN

    PRINT 'B表觸發器, 不處理'

    RETURN

END

 ASA:

--Create a table

CREATE TABLE t1 ( id integer NOT NULL,

all_amt numeric ,

remarks char,

);

--insert data

INSERT INTO "DBA"."t1" ("id","all_amt","remarks") VALUES(1,1222.000000,null)

INSERT INTO "DBA"."t1" ("id","all_amt","remarks") VALUES(1,12.000000,null)

INSERT INTO "DBA"."t1" ("id","all_amt","remarks") VALUES(1,1222.000000,null)

--Want to use a statement-level triggers, at the new insert data  total  the same ID's all_amt sum, and update the total results to remarks column

--I need the results as follows:：

select * from t1

id           all_amt                             remarks

1           1222.000000                         2456

1           12.000000                           2456 

1           1222.000000                         2456

Row Level Trigger:

ALTER TRIGGER "tr_calc_sum_amts_for_id_row_level_trigger" AFTER INSERT
ORDER 1 ON "DBA"."t1"
REFERENCING NEW AS new_name
FOR EACH ROW
BEGIN
    declare sum_all_amts_for_id numeric;
    select sum( t1.all_amt ) into sum_all_amts_for_id from t1 
        where id = new_name.id;
    update t1 set remarks = 
        cast( sum_all_amts_for_id  as char(50));
END
      

Statement Level Trigger:

ALTER TRIGGER "tr_calc_sum_amts_for_id_statement_level_trigger" AFTER INSERT
ORDER 1 ON "DBA"."t1"
 REFERENCING NEW AS new_name
FOR EACH STATEMENT
BEGIN
    declare curs_ids cursor for 
       select distinct id from new_name;
    declare curr_id integer;
    declare sum_all_amts_for_id numeric;
    open curs_ids;
    lp: LOOP
    fetch next curs_ids INTO curr_id;
    if sqlcode <> 0  then leave lp  end if;
    select sum( t1.all_amt ) 
       into sum_all_amts_for_id from t1 
    where id = curr_id;
    update t1 set remarks = 
         cast( sum_all_amts_for_id  as char(50))
    where id = curr_id;
    end loop;
    close curs_ids;
END