触发器递归的定义SQL

背景

AUPDATE后,取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 的控制。对于"AUPDATE 后,取B表某列再次UPDATE A",如果仅更新A表的某些列才触发UPDATE B, 并且B 表再次UPDATE A表不会包含A表触发UPDATE B的那些列,则在A表的触发器中,使用IF UPDATE()来确定是否应该UPDATE B即可。

2       使用@@NESTLEVEL

该变量值确定嵌套层数。

对于"Aupdate后,取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

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值