如果在存储过程中删除另外一个表的内容,该如何解决

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

SQL code

ALTER PROCEDURE [dbo].[DelNode](    @product_id varchar(25),    @node_id int)ASdeclare @lft intdeclare @rgt intif 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,0insert into bom select 2,1insert into bom select 3,1insert into bom select 4,3create table bomdetail(id int,bomid int)insert into bomdetail select 1,1insert into bomdetail select 2,1insert into bomdetail select 3,2insert into bomdetail select 4,3insert into bomdetail select 5,1insert into bomdetail select 6,2goalter table bomdetailadd constraint FK_det_bomforeign key(bomid) references bomon delete cascade  --级联删除on update cascadegodelete from bom where id=2  --删除bom表 id 为 2 的行select * from bomdetail  --子表中查询,已无bomid=2的行了/*id          bomid----------- -----------1           12           14           35           1(4 行受影响)*/godrop table bomdetail,bom 

 

查看更多 下一篇