日期:2014-05-18  浏览次数:20530 次

如果在存储过程中删除另外一个表的内容
我在一个存储过程中写
SQL code

ALTER PROCEDURE [dbo].[DelNode]
(
    @product_id varchar(25),
    @node_id int
)
AS
declare @lft int
declare @rgt int
if exists(select product_id, node_id from BOM where product_id = @product_id and node_id = @node_id)
    begin
        SET XACT_ABORT ON
        BEGIN TRANSACTION
            select @lft = lft, @rgt = rgt from BOM where product_id = @product_id and node_id = @node_id
            delete from BOM where product_id = @product_id and lft >= @lft and rgt <= @rgt
            update BOM set lft = lft - (@rgt - @lft + 1) where product_id = @product_id and lft > @lft
            update BOM set rgt = rgt - (@rgt - @lft + 1) where product_id = @product_id and rgt > @rgt
        COMMIT TRANSACTION
        SET XACT_ABORT OFF
    end



现在的问题是,在delete from BOM where product_id = @product_id and lft >= @lft and rgt <= @rgt
时需要删除另外一个表BOMDetail的内容,delete from BOMDetail where product_id = BOM.product_id and node_id = BOM.node_id。BOM表的记录不是一条,是多条
在存储过程中用的是TRANSACTION,所以不能使用触发器删除BOMDetail表的内容
请问,要如何写才能删除BOMDetail表的内容

------解决方案--------------------
不能用触发器 只能考虑用级联删除了。
------解决方案--------------------
探讨
级联删除怎么做?

------解决方案--------------------
探讨
引用:
级联删除怎么做?


--级联删除
create table ta(id int not null primary key)
insert ta
select 1

create table tb(id int foreign key references ta(id) on delete cascade)
insert tb
se……

------解决方案--------------------
给个例子你看看吧:
SQL code
CREATE TABLE bom(ID int primary key,pid int)
insert into bom select 1,0
insert into bom select 2,1
insert into bom select 3,1
insert into bom select 4,3
create table bomdetail(id int,bomid int)
insert into bomdetail select 1,1
insert into bomdetail select 2,1
insert into bomdetail select 3,2
insert into bomdetail select 4,3
insert into bomdetail select 5,1
insert into bomdetail select 6,2

go
alter table bomdetail
add constraint FK_det_bom
foreign key(bomid) references bom
on delete cascade  --级联删除
on update cascade
go
delete from bom where id=2  --删除bom表 id 为 2 的行
select * from bomdetail  --子表中查询,已无bomid=2的行了
/*
id          bomid
----------- -----------
1           1
2           1
4           3
5           1

(4 行受影响)

*/
go
drop table bomdetail,bom