我觉得如yxl1108(yxl1108)所说,做个存储过程,传个A表的主键进去, 在存储过程里面先用这个A表的主键删除B和C表。 可以不用游标,也不用一条条删除。比如: delete c where Bkey in (select BKey from B where AKey=@ParaAKey)delete B where AKey=@ParaAKeydelete A where AKey=@ParaAKey这里AKey,BKey,CKey是A,B,C三个表的主键(假设都只有一个字段,如果不是,可以把IN语句改成EXISTS语句),@ParaAKey是传过来的参数!
不要外键不行吗?去掉外键,在A和B上写delete trigger可以完成级连删除。
谢谢Haiwer(海阔天空) weixxxp(想一想) :我写的不行,要不然我也不至于这么头痛了。 CREATE TRIGGER [TR_project_del] ON [project] FOR DELETE AS Begin delete project_fundsource from project_fundsource,deleted where deleted.projectcode=project_fundsource.projectcode delete project_plan from project_plan,deleted where deleted.projectcode=project_plan.projectcode End go
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.No changes made to database.DELETE FROM project WHERE projectcode = '200201000006'
我在payout里面有一个删除触发器。如果去掉这个触发器,就正常。 能不能修改这个触发器 CREATE TRIGGER tr_payout_delete ON payout FOR Delete AS BEGIN Declare @payoutmoney_deleted numeric(12,2) Declare @planbalance numeric(12,2) Declare @payoutcode char(8) Declare @plancode char(8) begin Set @payoutcode=(Select payoutcode from deleted) Set @plancode=(Select plancode from deleted) Set @payoutmoney_deleted=(Select payoutmoney from deleted) end update project_plan set planbalance=IsNull(planbalance,0.00)+@payoutmoney_deleted where plancode=@plancode end
我C表中的数据也只能一条条删除的。否则报错。
Haiwer(海阔天空)大虾:能不能给个例子。感激不尽!!!
在存储过程里面先用这个A表的主键删除B和C表。
可以不用游标,也不用一条条删除。比如:
delete c
where Bkey in (select BKey from B where AKey=@ParaAKey)delete B
where AKey=@ParaAKeydelete A
where AKey=@ParaAKey这里AKey,BKey,CKey是A,B,C三个表的主键(假设都只有一个字段,如果不是,可以把IN语句改成EXISTS语句),@ParaAKey是传过来的参数!
weixxxp(想一想) :我写的不行,要不然我也不至于这么头痛了。
CREATE TRIGGER [TR_project_del] ON [project]
FOR DELETE
AS
Begin
delete project_fundsource from project_fundsource,deleted where deleted.projectcode=project_fundsource.projectcode
delete project_plan from project_plan,deleted where deleted.projectcode=project_plan.projectcode End
go
能不能修改这个触发器
CREATE TRIGGER tr_payout_delete ON payout FOR
Delete AS
BEGIN
Declare @payoutmoney_deleted numeric(12,2)
Declare @planbalance numeric(12,2)
Declare @payoutcode char(8)
Declare @plancode char(8)
begin
Set @payoutcode=(Select payoutcode from deleted)
Set @plancode=(Select plancode from deleted)
Set @payoutmoney_deleted=(Select payoutmoney from deleted)
end
update project_plan set planbalance=IsNull(planbalance,0.00)+@payoutmoney_deleted where plancode=@plancode
end