请大家帮我看看一个删除的触发器(delete)

[tristsesame]

表结构是这样子的.
一个帐号表(tab_account):accountID
一个用户表(tab_user):userID

它们的关系是一对多，即一帐号对应多用户.

当删除用户时（可以成批删除用户)，
判断此用户相关连的帐号在用户表中是否还有相对应的用户.
如果没有，则删除该帐号。

我是这样写的这个触发器:
CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
AS

DECLARE @IsDelAcct int;
DECLARE @del_value int

DECLARE del_cur CURSOR LOCAL FOR SELECT acctountID FROM deleted
OPEN del_cur
FETCH NEXT FROM del_cur INTO @del_value
WHILE @@fetch_status = 0
BEGIN
SELECT @IsDelAcct = COUNT(*) FROM tab_user WHERE accountID = @del_value
IF @IsDelAcct = 0
BEGIN
DELETE FROM tab_account WHERE accountID = @del_value
END

FETCH NEXT FROM del_cur INTO @del_value
END
CLOSE del_cur
DEALLOCATE del_cur

只是我一直有些疑惑，总觉得应该有更好的方法.
向大家请教.

[pengdali]

这个:


CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
AS
DELETE tab_account WHERE accountID exists (SELECT 1 FROM deleted where acctountID=tab_account.acctountid not exists (select 1 from tab_user where acctountID=deleted.acctountID))

[pengdali]

CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
AS
DELETE tab_account WHERE exists (SELECT 1 FROM deleted where acctountID=tab_account.acctountid not exists (select 1 from tab_user where acctountID=deleted.acctountID))

[pengdali]

CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
AS
DELETE tab_account WHERE exists (SELECT 1 FROM deleted where acctountID=tab_account.acctountid and not exists (select 1 from tab_user where acctountID=deleted.acctountID))

[teaism]

哈哈....但多重exists我个人认为比表连接效率要低(在deleted数据量较大时)。
多重exists一般不建议常用，因为逻辑上不易阅读，且效率不见得会高。

个人观点，别生气。

[zdleek]

mark

[guangli_zhang]

delete from tab_account where accountID = deleted.accountID and (select count(*) from userID where userID.accountID = deleted.accountID and userID.userID<> deleted.userID) = 0

关键是 一个帐号 对应 多个用户 ,and 一个用户 对应 一个帐号

[wzh1215]

teaism()的方法行得通，不过大力的更易理解

[sdhdy]

CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
AS
if not exists(select a.accountID from tab_user a,deleted b where a.accountID=b.accountID)
DELETE FROM tab_account WHERE accountID = (select accountID from deleted)

[yoki]

CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
AS
if not exists(select a.accountID from tab_user a,deleted b where a.accountID=b.accountID)
DELETE FROM tab_account WHERE accountID = (select distinct accountID from deleted)

[teaism]

if not exists不行.
这样才是正确:

CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
DELETE FROM tab_account A,deleted B WHERE A.accountID=B.accountID and not exists(select 1 from from tab_user where accountID=B.accountID)

[yoki]

if not exists不行.??不会吧？？

[teaism]

批量情况下不行。
if not exists(条件) ，就是条件一个都不存在时才执行delete语句，
条件部分存在时不执行。不符合楼主题意。

所以只有用表关联，把not exists 放到语句中去：

CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
DELETE FROM tab_account A,deleted B WHERE A.accountID=B.accountID and not exists(select 1 from from tab_user where accountID=B.accountID)

[sdhdy]

请问teaism() ，试过批量删除的情况吗？
批量删除是触发DELETE触发器一次还是多次？

[tristsesame]

我觉得批量删除应该是触发DELETE一次

我刚开始是没有用游标,是这样写的
SELECT @IsDelAcct = COUNT(*) FROM tab_user WHERE accountID IN (SELECT accountID FROM DELETED)
IF @IsDelAcct = 0
BEGIN
DELETE FROM tab_account WHERE accountID IN (SELECT accountID FROM DELETED)
END

结果就是若是成批删除的话，
对帐号不会有影响.

使用游标后，才能正确的删除帐号.

[sdhdy]

如果用的是delete aaa where f1 in (11,12,13)这种方式，确实不行！
不知楼主用的是哪种方式？

[tristsesame]

to teaism()

你的方法应该可行，
我呆会试试

[teaism]

--二位不信我们测试一下：
create table tab_account(accountID int)
insert tab_account select 1
union all select 2
union all select 3
union all select 4
go
create table tab_user(userid int,accountID int)
insert tab_user select 1,1
union all select 2,3
union all select 3,4
union all select 4,3

go
CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
AS
if not exists(select a.accountID from tab_user a,deleted b where a.accountID=b.accountID)
DELETE FROM tab_account WHERE accountID in (select distinct accountID from deleted)
--这边把你们的等号改为in(因为是一结多的关系等号不行)
go

delete tab_user where userid>2

select * from tab_account
/*这时应该把accountID=4删了，但没删，所以有错!
accountID
-----------
1
2
3
4
*/
drop trigger tr_Del_User
drop table tab_account

drop table tab_user
go

create table tab_account(accountID int)
insert tab_account select 1
union all select 2
union all select 3
union all select 4
go
create table tab_user(userid int,accountID int)
insert tab_user select 1,1
union all select 2,3
union all select 3,4
union all select 4,3

go
CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
as
DELETE A FROM tab_account A,deleted B WHERE A.accountID=B.accountID and not exists(select 1 from tab_user where accountID=B.accountID)
go

delete tab_user where userid>2

select * from tab_account
/*这才是正确的：
accountID
-----------
1
2
3
*/


drop trigger tr_Del_User
drop table tab_account
drop table tab_user

go

[pengdali]

这样就可以了：

CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
AS
DELETE tab_account WHERE accountID in (SELECT acctountID FROM deleted where acctountID in (select acctountID from tab_user))

[pengdali]

呵呵，来晚了

[teaism]

pengdali(大力 V3.0) 也要改一改：
CREATE TRIGGER tr_Del_User ON [dbo].[tab_user]
FOR DELETE
AS
DELETE tab_account WHERE accountID in (SELECT acctountID FROM deleted where acctountID NOT in (select acctountID from tab_user))
--是NOT IN