--可以,不过递归不可以超过32层 alter database 数据库名 set recursive_triggers on with no_wait; --例: set nocount on; alter database KWHZ set recursive_triggers on with no_wait; if object_id('t') is not null drop table t; if object_id('updtimes') is not null drop table updtimes; create table t(id int,col varchar(20)); create table updtimes(cou int); insert into updtimes values(0); declare @count int; select @count=0 while @count<10 begin insert into t values(@count,'col') select @count=@count+1; end go create trigger t_upd on t for update as if exists(select 1 from t where t.col='col') begin update t set col=col+'i' where id in(select top 1 id from t where col='col') update updtimes set cou=cou+1 end go update t set col='xx' where id=1; select * from updtimes /* 更新次数 ----------- 9 */
我在sql2008下只触发了一次啊。
表结构(单表目录树) nodeid pid 1 null 0 2 1 0 3 2 0 4 3 0 触发器 DECLARE @PID varchar(4) SELECT @PID=PID from Inserted; update database set Mark=1 where NodeID=@PID执行 update database set Mark=1 where NodeID=4 为什么只触发了一次,记录3和4被修改我1呢?
--可以,不过递归不可以超过32层
alter database 数据库名 set recursive_triggers on with no_wait;
--例:
set nocount on;
alter database KWHZ set recursive_triggers on with no_wait;
if object_id('t') is not null drop table t;
if object_id('updtimes') is not null drop table updtimes;
create table t(id int,col varchar(20));
create table updtimes(cou int);
insert into updtimes values(0);
declare @count int;
select @count=0
while @count<10
begin
insert into t values(@count,'col')
select @count=@count+1;
end
go
create trigger t_upd on t for update
as
if exists(select 1 from t where t.col='col')
begin
update t set col=col+'i'
where id in(select top 1 id from t where col='col')
update updtimes set cou=cou+1
end
go
update t set col='xx' where id=1;
select * from updtimes
/*
更新次数
-----------
9
*/
我在sql2008下只触发了一次啊。
表结构(单表目录树)
nodeid pid
1 null 0
2 1 0
3 2 0
4 3 0
触发器 DECLARE @PID varchar(4)
SELECT @PID=PID from Inserted;
update database set Mark=1 where NodeID=@PID执行
update database set Mark=1 where NodeID=4
为什么只触发了一次,记录3和4被修改我1呢?
哦?意思触发器里 的UPDATE不能在被触发了吗?