简单的触发器问题(二次发贴了)

[lx47]

CREATE TRIGGER del ON dbo.CreditInsurance
FOR DELETE
AS
delete from Gatheringprawn
where Gatheringprawn.insurecode = (select insurecode from deleted)

CreditInsurance 是财务总表，Gatheringprawn是流水帐表
我想在总表每一次被删除一条记录后，都把他对应的流水帐全部删除，
这个触发器老是报错，说(select insurecode from deleted)的返回值不止一条。
请高人指点一二。
报错：subquery returned more than 1 value
this is not permitted when the subquery fallows = <,<=,>=.>
or the subquery is uesed as an expression
我都试过好多种写法了，全都报同一个错

[westbulls]

试试这样呢?
CREATE TRIGGER del ON dbo.CreditInsurance
FOR DELETE
AS
declare @insurecode as varchar(50)--请定义为你的insurecode类型
select @insurecode=insurecode from deleted

delete from Gatheringprawn
where Gatheringprawn.insurecode =@insurecode

[txlicenhe]

CREATE TRIGGER del ON dbo.CreditInsurance
FOR DELETE
AS
delete Gatheringprawn
where Gatheringprawn.insurecode in (select insurecode from deleted)

[yujohny]

CREATE TRIGGER del ON dbo.CreditInsurance
FOR DELETE
AS
set nocount on
delete from Gatheringprawn
where insurecode in (select insurecode from deleted)

[pengdali]

CREATE TRIGGER del ON dbo.CreditInsurance
FOR DELETE
AS
delete Gatheringprawn where insurecode in (select insurecode from deleted)

[lx47]

还有，这个 set nocount on 有什么作用啊？

[lx47]

westbulls(westbulls)，pengdali(大力 V3.0)，yujohny(踏网无痕) ，txlicenhe(马可) ：
谢谢你们的回帖，你们的方法我一个一个都试了，还是报错，我快晕死了。谢谢大家了。

[sdhdy]

--这个应该没问题，删除的时候你是一条一条删除的吧？
--应该不是触发器的原因，还有别的触发器吗？
--可能是SQL SERVER的问题
--如果没有触发器的话，正常删除有问题吗？
--是不是日志空间满了？
CREATE TRIGGER del ON dbo.CreditInsurance
FOR DELETE
AS
delete Gatheringprawn from deleted where Gatheringprawn.insurecode =deleted.insurecode

[westbulls]

我觉得可能是你的gatheringprawn表中还有删除触发器,这个错误是它报的,你最好看一看.

[lx47]

不，大家把问题想复杂了，就这么简单一个表，就一个触发器，我一条一条的删除，删掉这个触发器就没问题，或者：
CREATE TRIGGER [TRIGGER NAME] ON [CreditInsurance]
FOR DELETE
AS
set nocount on
declare @insurcode varchar(20)
select @insurcode = (select insurecode from deleted)

--delete Gatheringprawn
--where Gatheringprawn.insurecode = @insurcode
--set nocount on
--delete Gatheringprawn from deleted where Gatheringprawn.insurecode = deleted.insurecode
也没问题，就是只要执行被注释的代码，一准儿报错。

[lionstar]

你执行
alter table CreditParam
add constraint fk_CreditInsurance foreign key (insurecode)
references CreditInsurance(insurecode)
on delete cascade
在CreditParam创建外键，并使用级联删除。
然后不使用触发器，直接执行delete CreditInsurance where 条件表达式
CreditParam表相应记录就可以自动被删除了。

[lx47]

就是一有删除Gatheringprawn表就报错.......


哦，我看了一下，Gatheringprawn表里是有触发器，晕，是他报错。
那我该把分数给谁呢。。大家都出力了。

[zjcxc]

原来是楼主搞错了.
我说呢,大家写的都没错嘛.


平无分配就行了

[lx47]

呵呵，不好意思啊，麻烦大家了。
另外 TO：sdhdy(大江东去...)
SQLSERVER中，记录即使是一批一批的删除，在触发DELETE的触发器的时候，是出发多少次呢？是一次还是与删除的记录数有关（相等）呢？