CREATE TRIGGER [TRI_del] ON [TB]
FOR delete
AS
DECLARE @MC VARCHAR(14)
SELECT @MC=名称 from deletedDELETE tb WHERE 名称 like '+@mc+%'
FOR delete
AS
DECLARE @MC VARCHAR(14)
SELECT @MC=名称 from deletedDELETE tb WHERE 名称 like '+@mc+%'
select 1,'a',null
union
select 2,'aa',1
union
select 3,'bb',2
union
select 4,'aaa1',2
union
select 5,'aaa2',2
union
select 6,'aaa1a',4
union
select 7,'aaa1aa',5
-----
CREATE TRIGGER [TRI_del] ON [TB]
FOR delete
AS
DECLARE @MC VARCHAR(14)
SELECT @MC=序号 from deletedDELETE tb WHERE 上级序号=@mc-----
delete tb where 序号=1----
select * from tb---------------
序号 名称 上级序号3 BB 2
4 AAA1 2
5 AAA2 2
6 AAA1A 4
7 AAA1AA 5
select 1,'a',null
union
select 2,'aa',1
union
select 3,'bb',1
union
select 4,'aaa1',2
union
select 5,'aaa2',2
union
select 6,'aaa1a',4
union
select 7,'aaa1aa',5
序号 名称 上级序号 | A
1 A | |——BB
2 AA 1 | ——AA
3 BB 1 | |——AAA2
4 AAA1 2 | ——AAA1
5 AAA2 2 | |——AAA1A
6 AAA1A 4 | |——AAA1AA
7 AAA1AA 5 |
形成一棵树:
要求外部删除一记录时,通过触发该触发器删除它的所有子孙。比如:我删除了序号为2的记录,那么序号为4、5、6、7的均应该删除。因为序号4、5是2的孩子,而序号6是4的孩子,也是2的子孙呀,以此类推。
你不能一个一个的删! 否则会丢失上下级父子关系!
你要找到特定节点的所有后代节点(临时表)!delete T
where id in (select ....) 参考 联机手册!
怎么不能一个一个的删呢?比如我删除了序号2的记录,从deleted表取出序号2,再去查找上级序号为2的所有记录,以此一步一步的找完,确实很麻烦,可能要用到递归调用,但我不知道触发器能否递归调用?
SET NOCOUNT ON
DECLARE @lvl int, @line char(20)
CREATE TABLE #stack (item char(20), lvl int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @lvl = 1
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN
SELECT @current = item
FROM #stack
WHERE lvl = @lvl
SELECT @line = space(@lvl - 1) + @current --
PRINT @line
-- 改成
delete Hierarchy where Parent = @current
-- DELETE FROM #stack
WHERE lvl = @lvl
AND item = @current
INSERT #stack
SELECT Child, @lvl + 1
FROM Hierarchy
WHERE Parent = @current
IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1
END
ELSE
SELECT @lvl = @lvl - 1
END -- WHILE
很典型,不能处理一次删除多条记录的情况。--我想,不能用名称来判断CREATE TRIGGER [TRI_del] ON [TB]
FOR delete
AS
DECLARE @t Table (序号 int)
insert @t
select 序号 from [tb] where 上级序号 in (SELECT 序号 from deleted)while exists (
select 序号 from [tb] where 上级序号 in (SELECT 序号 from @t)
and 序号 not in (SELECT 序号 from @t)
)
insert @t
select 序号 from [tb] where 上级序号 in (SELECT 序号 from @t)
and 序号 not in (SELECT 序号 from @t)
delete [tb]
where 序号 in (SELECT 序号 from @t)go
触发器本身就是递归的!
RECURSIVE_TRIGGERS ON | OFF
trigger 有一句话也许就可以了:delete T
where 上级序号 = (select 序号 from deleted)
declare @t table ([ID] int,[Name] char (10),[ParentID] int )set nocount on
insert into @t
select 1,'a',null
union
select 2,'aa',1
union
select 3,'bb',1
union
select 4,'aaa1',2
union
select 5,'aaa2',2
union
select 6,'aaa1a',4
union
select 7,'aaa1aa',5select '输出删除前记录'
select * from @tdeclare @ID int
set @ID = 2 --这里输入删除的根结点IDdeclare @level int, @name varchar(20)
set @level = 1
declare @stack table( [ID] int, [level] int )
insert into @stack select @ID,@level
select '执行过程'
while @level > 0
begin
if exists( select 1 from @stack where level = @level )
begin
select top 1 @id = id from @stack where level = @level
select @name = [name] from @t where id=@id
print @name + '已删除'
delete from @t where id=@id
delete from @stack where id = @id
insert into @stack select [id], @level + 1 from @t where parentid=@id
if @@rowcount > 0
set @level = @level + 1
end
else
set @level = @level - 1
endselect '输出删除后记录'select * from @t/*
结果:
--------------
输出删除前记录ID Name ParentID
----------- ---------- -----------
1 a NULL
2 aa 1
3 bb 1
4 aaa1 2
5 aaa2 2
6 aaa1a 4
7 aaa1aa 5
--------
执行过程aa 已删除
aaa1 已删除
aaa1a 已删除
aaa2 已删除
aaa1aa 已删除
--------------
输出删除后记录ID Name ParentID
----------- ---------- -----------
1 a NULL
3 bb 1*/
DATABASE pubs SET RECURSIVE_TRIGGERS ON
GO
delete from table1 where col1='BX001'是不能连环删除的!@ :>>>
上面不用ALTER
DATABASE pubs SET RECURSIVE_TRIGGERS ON
GO
也可以连环删除
returns @re table(id varchar(7) primary key,floors int)
as
begin
set @floors=0
insert @re select @floors
while @@rowcount>0
begin
set @floors=@floors+1
insert @re select a.id,@floors from shinz_gryjzl as a,@re as b where a.pid=b.id and b.floors=@floors-1
end
return
endselect * into 临时表 from getchild(id)
delete from 表,临时 where 表.id=临时表.idpid为父点
FOR delete
ASdelete T
where 上级序号 in (select 序号 from deleted)
select 1,'a',null
union
select 2,'aa',1
union
select 3,'bb',2
union
select 4,'aaa1',2
union
select 5,'aaa2',2
union
select 6,'aaa1a',4
union
select 7,'aaa1aa',5
create trigger tri_del on tb
for delete
as
delete from tb where 上级序号 in (select 序号 from deleted)delete from tb where 名称='aaa1'
deleteP(f删除的记录编号@@Pid)
select * from aaa where pid=@pid
如果选处理的记录条数》0 ,说明嘘父,yao在递归
delete T
where 上级序号 = (select 序号 from deleted)
绝对可以,我在sqlserver2000上已经应用过了,你再试一试
它是递归的
使用嵌套存储过程 在存储过程的开头 关闭表的删除触发器,结束 要打开删除触发器
create procedure up_deletenode @id numeric(18,0)
as
Declare @childid numeric(18,0)
Alter table 表 disable trigger all
declare id_cursor cursor for Select 序号 from 表 where 上级序号 = @id
open id_curosr
fetch next from id_curosr into @chilid
while @@fetch_status = 0
exec up_deletenode @chilid
close id_cursor
deallocate id_curosr
delete from 表 where id = @id
Alter table 表 enable trigger all
“在 RECURSIVE_TRIGGERS ON 的情况下”
这句话怎么理解,怎样操作,请指教。
我设置了RECURSIVE_TRIGGERS ON ,可以递归调用,但当删除一项时,提示“”,删除不了,该怎么解决呀?
我设置了RECURSIVE_TRIGGERS ON ,可以递归调用,但当删除一项时,提示“超出了触发器的最大嵌套层数(最大层数为32)”,删除不了,该怎么解决呀?
FOR DELETE
AS
/* 方法1 如果数据库的RECURSIVE_TRIGGERS(直接递归参数) 设为on 则
if ( select count(fid) from ttree where fupid in (select fid from DELETED))=0 return
delete from ttree where fUPid in ( select fid from DELETED )*/--方法2 如果数据库的RECURSIVE_TRIGGERS(直接递归参数) 设为off 则
select fid into #t1 from ttree where fupid in ( select fid from DELETED )
while (select count(fid) from #t1 )>0
begin
delete from ttree where fid in (select fid from #t1 )
select fid into #t2 from ttree where fUPid in ( select fid from #t1 )
delete from #t1
insert into #t1(fid ) select fid from #t2
DROP TABLE #t2
end
//测试数据
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Ttree]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Ttree]
GOCREATE TABLE [dbo].[Ttree] (
[fid] [int] NOT NULL ,
[fdesc] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[fupid] [int] NULL
) ON [PRIMARY]
GOINSERT into ttree(fid,fdesc) values(1 ,'A' )
INSERT into ttree(fid,fdesc,fupid) values(2,'AA', 1 )
INSERT into ttree(fid,fdesc,fupid) values(3 ,'BB', 1 )
INSERT into ttree(fid,fdesc,fupid) values(4,'AAA1',2)
INSERT into ttree(fid,fdesc,fupid) values(5,'AAA2',2)
INSERT into ttree(fid,fdesc,fupid) values(6, AAA1A',4)
INSERT into ttree(fid,fdesc,fupid) values(7,'AAA1AA',5)