请高手来挑战,触发器每次只能删除一条数据
两个表建立了外键,在删除主表数据的时候我用了触发器先删除了从表中的含相关字段的数据,结果发现触发器每次只能删除从表中含主表中字段的一条数据,是什么原因?形如:在从表中有两条外键字段ID1为1的数据,那么我来删除从表中ID1为1的数据行,结果每运行一次只能删除一条,
触发器如下:
create trigger trDeleteDetailCate
on 主表
instead of delete
as
declare @cateDetailId int
select @cateDetailId =cateDetailId from deleted
delete 从表 where cateDetailId=@cateDetailId
我要怎么改进呢,是不是我在表的什么地方设置错了??
两个表建立了外键,在删除主表数据的时候我用了触发器先删除了从表中的含相关字段的数据,结果发现触发器每次只能删除从表中含主表中字段的一条数据,是什么原因?形如:在从表中有两条外键字段ID1为1的数据,那么我来删除从表中ID1为1的数据行,结果每运行一次只能删除一条,
触发器如下:
create trigger trDeleteDetailCate
on 主表
instead of delete
as
declare @cateDetailId int
select @cateDetailId =cateDetailId from deleted
delete 从表 where cateDetailId=@cateDetailId
我要怎么改进呢,是不是我在表的什么地方设置错了??
from 从表 as a
join deleted as b
on a.cateDetailId = b.cateDetailId
在对从表没有完全删除以前对主表进行删除的话就出现了冲突,所以我就只测试了一下对从表的操作
(
ThemeID int primary key,
ThemeName varchar(100),
)create table Users
(
UserID int primary key,
UserName varchar(100),
ThemeID int constraint Users_ThemeID_FK references Themes(ThemeID)
)insert into Themes (ThemeID, ThemeName) values (1,'Default')
insert into Themes (ThemeID, ThemeName) values (2,'Winter')insert into Users(UserID, UserName, ThemeID) values (1,'JSmith',1)
insert into Users(UserID, UserName, ThemeID) values (2,'Ted',1)
insert into Users(UserID, UserName, ThemeID) values (3,'Mary',2)GOCREATE TRIGGER tri ON Themes
INSTEAD OF DELETE
AS
--删从表
DELETE A
FROM Users AS A
JOIN deleted AS B
ON A.ThemeId = B.ThemeId;
--删主表
DELETE A
FROM Themes AS A
JOIN deleted AS B
ON A.ThemeId = B.ThemeId;
GODELETE Themes WHERE ThemeId=1SELECT * FROM Themes;
SELECT * FROM UsersGO
drop table users,themes
(
ThemeID int primary key,
ThemeName varchar(100),
)create table Users
(
UserID int primary key,
UserName varchar(100),
ThemeID int constraint Users_ThemeID_FK references Themes(ThemeID)
on delete cascade --联级删除
)insert into Themes (ThemeID, ThemeName) values (1,'Default')
insert into Themes (ThemeID, ThemeName) values (2,'Winter')insert into Users(UserID, UserName, ThemeID) values (1,'JSmith',1)
insert into Users(UserID, UserName, ThemeID) values (2,'Ted',1)
insert into Users(UserID, UserName, ThemeID) values (3,'Mary',2)GO
DELETE Themes WHERE ThemeId=1SELECT * FROM Themes;
SELECT * FROM UsersGO
drop table users,themes
但如果删除多个ID时..得到的是个集合..那一个集合放到一个变量中.
一个变量只存放一个值.那你是将这个集合的这些值的哪个放到这个变量中?.