• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

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

tristsesame 杭州快睿 开发组长/高级工程师/技术专家  2003-10-22 10:29:47
表结构是这样子的.
一个帐号表(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

只是我一直有些疑惑,总觉得应该有更好的方法.
向大家请教.
...全文
15 点赞 收藏 22
写回复
22 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
teaism 2003-10-22
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
回复
pengdali 2003-10-22
呵呵,来晚了
回复
pengdali 2003-10-22
这样就可以了:

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))
回复
teaism 2003-10-22
--二位不信我们测试一下:
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
回复
tristsesame 2003-10-22
to teaism()

你的方法应该可行,
我呆会试试
回复
sdhdy 2003-10-22
如果用的是delete aaa where f1 in (11,12,13)这种方式,确实不行!
不知楼主用的是哪种方式?
回复
tristsesame 2003-10-22
我觉得批量删除应该是触发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 2003-10-22
请问teaism() ,试过批量删除的情况吗?
批量删除是触发DELETE触发器一次还是多次?
回复
teaism 2003-10-22
批量情况下不行。
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)
回复
yoki 2003-10-22
if not exists不行.??不会吧??
回复
teaism 2003-10-22
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 2003-10-22
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)
回复
sdhdy 2003-10-22
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)
回复
wzh1215 2003-10-22
teaism()的方法行得通,不过大力的更易理解
回复
guangli_zhang 2003-10-22
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 一个用户 对应 一个帐号
回复
zdleek 2003-10-22
mark
回复
teaism 2003-10-22
哈哈....但多重exists我个人认为比表连接效率要低(在deleted数据量较大时)。
多重exists一般不建议常用,因为逻辑上不易阅读,且效率不见得会高。

个人观点,别生气。
回复
pengdali 2003-10-22
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))
回复
pengdali 2003-10-22

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 2003-10-22
这个:


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))
回复
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2003-10-22 10:29
社区公告
暂无公告