在有外键的情况下,怎么用触发器级联删除\修改

本文转载自 creatdatabase 查看原文 2011/03/22 14 修改/ 外键/ 触发器/ 删除



//说明.当我删除或者修改cat_tb的时候,sub_tb对于的外键也随着改变

  功能要求:用触发器去实现这个功能

  代码如下:



create table cat_tb

(

  cat_id int primary key,

  cat_name varchar(20)

)

go

insert into cat_tb values(100,'数码电子')

insert into cat_tb values(200,'化妆品')

insert into cat_tb values(300,'家具')

go



create table sub_tb

(

 sub_id int primary key,

 sub_name varchar(20),

 cat_id int foreign key(cat_id) references cat_tb

)



go 

insert into sub_tb values(1000,'电脑',100)

insert into sub_tb values(2000,'鼠标',100)

insert into sub_tb values(3000,'美容品',200)

insert into sub_tb values(4000,'桌子',300)

insert into sub_tb values(5000,'凳子',300)

go



create trigger  cat_sub

on cat_tb

after delete,update

as

if exists(select 1 from inserted) and exists(select 1 from deleted)

 begin 

   update sub_tb set cat_id=(select cat_id from inserted) where cat_id=(select cat_id from deleted)  

 print '修改成功 ' 

 end



else if exists(select 1 from deleted)

begin 

  delete sub_tb from deleted where sub_tb.cat_id=(select deleted.cat_id from deleted)

 print '删除成功'

end

update cat_tb set cat_id=400 where cat_id=300
服务器: 消息 547，级别 16，状态 1，行 1
UPDATE 语句与 COLUMN REFERENCE 约束 'FK__sub_tb__cat_id__36BC0F3B' 冲突。该冲突发生于数据库 'master'，表 'sub_tb', column 'cat_id'。
语句已终止。

在有外键的情况下,怎么用触发器级联删除\修改

23 个解决方案

#1

如果报外键冲突，你就可以直接用外键的级联功能。反而触发器不能实现了。

#2

用級聯刪除更新

#3

要么就删除主外键关系

#4

create table cat_tb

(

  cat_id int primary key,

  cat_name varchar(20)

)

go

insert into cat_tb values(100,'数码电子')

insert into cat_tb values(200,'化妆品')

insert into cat_tb values(300,'家具')

go



create table sub_tb

(

 sub_id int primary key,

 sub_name varchar(20),

 cat_id int foreign key(cat_id) references cat_tb(cat_id) ON DELETE CASCADE ON UPDATE CASCADE

)

go

DROP TABLE sub_tb,cat_tb

#5

引用 4 楼 roy_88 的回复:

SQL code
create table cat_tb
(
cat_id int primary key,
cat_name varchar(20)
)
go
insert into cat_tb values(100,'数码电子')
insert into cat_tb values(200,'化妆品')
insert into cat_tb values(300,'……

#6

引用 3 楼 fredrickhu 的回复:

要么就删除主外键关系

不删除外键.我现在需要的是有外键的情况下,不用on delete cascade ,on delete cascade
情况下,怎么用触发器去实现呢?多谢

#7

引用 4 楼 roy_88 的回复:

SQL code
create table cat_tb
(
cat_id int primary key,
cat_name varchar(20)
)
go
insert into cat_tb values(100,'数码电子')
insert into cat_tb values(200,'化妆品')
insert into cat_tb values(300,'家具')
go……

有外键的情况下,不用on delete cascade ,on delete cascade.
怎么去用触发去实现器删除\修改

#8

禁用约束
删除数据
启用约束

#9

当有外键的时候，
修改主表主键，如果此主键被子表引用，不能修改，修改就报错。
修改子表外键，如果修改后的外键在主表中不存在，也会报错。

所有你的需求是达不到的

#10

引用 8 楼 dearbinge 的回复:

禁用约束
删除数据
启用约束

虽然你说的很有道理,那么要怎么
去实现呢?

#11

引用 9 楼 xiao_ai_mei 的回复:

当有外键的时候，
修改主表主键，如果此主键被子表引用，不能修改，修改就报错。
修改子表外键，如果修改后的外键在主表中不存在，也会报错。

所有你的需求是达不到的

难道就没办法实现吗?有事务呢?是否可以实现呢

#12

非要這樣做，可以利用 null的特點處理

create table cat_tb

(

  cat_id int primary key,

  cat_name varchar(20)

)

go

insert into cat_tb values(100,'数码电子')

insert into cat_tb values(200,'化妆品')

insert into cat_tb values(300,'家具')

go



create table sub_tb

(

 sub_id int primary key,

 sub_name varchar(20),

 cat_id int foreign key(cat_id) references cat_tb

)



go 

insert into sub_tb values(1000,'电脑',100)

insert into sub_tb values(2000,'鼠标',100)

insert into sub_tb values(3000,'美容品',200)

insert into sub_tb values(4000,'桌子',300)

insert into sub_tb values(5000,'凳子',300)

go



CREATE trigger  cat_sub

on cat_tb

INSTEAD OF delete,update

as

if exists(select 1 from inserted) and exists(select 1 from deleted)

 begin 

	update sub_tb set cat_id=null where cat_id=(select TOP 1  cat_id from deleted)

    UPDATE cat_tb SET cat_id=(select TOP 1 cat_id from inserted) where cat_id=(select TOP 1  cat_id from deleted)

    update sub_tb set cat_id=(select TOP 1 cat_id from inserted) where cat_id IS NULL

	print '修改成功 ' 

 end

else if exists(select 1 from deleted)

begin 

  delete sub_tb  where  EXISTS(select 1 from DELETED WHERE cat_id=sub_tb.cat_id)

  DELETE cat_tb WHERE EXISTS(SELECT 1 FROM DELETED WHERE cat_id=cat_tb.cat_id)

 print '删除成功'

end

GO

update cat_tb set cat_id=400 where cat_id=300

go



SELECT * FROM sub_tb--查看結果



/*

SELECT * FROM sub_tb

*/

DROP TABLE sub_tb,cat_tb

#13

--> 测试数据: cat_tb

if object_id('cat_tb') is not null drop table cat_tb

go

create table cat_tb

(

  cat_id int primary key,

  cat_name varchar(20)

)

go

insert into cat_tb values(100,'数码电子')

insert into cat_tb values(200,'化妆品')

insert into cat_tb values(300,'家具')

go

if object_id('sub_tb') is not null drop table sub_tb

go

create table sub_tb

(

 sub_id int primary key,

 sub_name varchar(20),

 cat_id int constraint FK1	--稍微改动 

	foreign key(cat_id) references cat_tb

)

 --alter table sub_tb drop constraint fk1

go 

insert into sub_tb values(1000,'电脑',100)

insert into sub_tb values(2000,'鼠标',100)

insert into sub_tb values(3000,'美容品',200)

insert into sub_tb values(4000,'桌子',300)

insert into sub_tb values(5000,'凳子',300)

go

if object_id('cat_sub') is not null drop trigger cat_sub

go 

create trigger  cat_sub

on cat_tb

after delete,update

as

if exists(select 1 from inserted) and exists(select 1 from deleted)

 begin 

   update sub_tb set cat_id=(select cat_id from inserted) where cat_id=(select cat_id from deleted)  

   print '修改成功 ' 

 end



else if exists(select 1 from deleted)

begin 

  delete sub_tb from deleted where sub_tb.cat_id=(select deleted.cat_id from deleted)

 print '删除成功'

end



go 



ALTER TABLE sub_tb NOCHECK CONSTRAINT FK1

update cat_tb set cat_id=400 where cat_id=300

ALTER TABLE sub_tb  CHECK CONSTRAINT FK1

#14

引用 12 楼 roy_88 的回复:

非要這樣做，可以利用null的特點處理

SQL code
create table cat_tb
(
cat_id int primary key,
cat_name varchar(20)
)
go
insert into cat_tb values(100,'数码电子')
insert into cat_tb values(200,'化妆品')
insert into cat……

你说的意思,如果真的要这样子,是否有弊端呢?多多指教

#15

引用 10 楼 creatdatabase 的回复:

引用 8 楼 dearbinge 的回复:

禁用约束
删除数据
启用约束

虽然你说的很有道理,那么要怎么
去实现呢?



EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'--禁用约束

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  --启用约束

#16

引用 14 楼 creatdatabase 的回复:

引用 12 楼 roy_88 的回复:

非要這樣做，可以利用null的特點處理

SQL code
create table cat_tb
(
cat_id int primary key,
cat_name varchar(20)
)
go
insert into cat_tb values(100,'数码电子')
insert into cat_tb values(……

樓主的情況,建議級聯更新刪除處理，少用觸發器，不便於控制。
能不用最好不要用。
方法是沒有問題，如果批量更新如:
update cat_tb set cat_id=cat_id+400
------------
這樣就只能用游標和循環。效率不高。多用戶操作並發的情況也需要考慮。

#17

引用 16 楼 roy_88 的回复:

引用 14 楼 creatdatabase 的回复:

引用 12 楼 roy_88 的回复:

非要這樣做，可以利用null的特點處理

SQL code
create table cat_tb
(
cat_id int primary key,
cat_name varchar(20)
)
go
insert into cat_tb values(100,'数码电子……

其实当初我看到这个问题的时候,我看了一篇数据工程师的面试题,想了很久,都想不出来.
因此我才发帖,问下是否有办法去实现.多谢

#18

引用 12 楼 roy_88 的回复:

非要這樣做，可以利用null的特點處理

SQL code
create table cat_tb
(
cat_id int primary key,
cat_name varchar(20)
)
go
insert into cat_tb values(100,'数码电子')
insert into cat_tb values(200,'化妆品')
insert into cat……

我测试过了,还是不行的,提示
UPDATE 语句与 COLUMN REFERENCE 约束 'FK__sub_tb__cat_id__36BC0F3B' 冲突。该冲突发生于数据库 'master'，表 'sub_tb', column 'cat_id'。
语句已终止。

#19

本帖最后由 roy_88 于 2011-03-22 16:03:08 编辑

引用 17 楼 creatdatabase 的回复:

引用 16 楼 roy_88 的回复:

引用 14 楼 creatdatabase 的回复:

引用 12 楼 roy_88 的回复:

非要這樣做，可以利用null的特點處理

SQL code
create table cat_tb
(
cat_id int primary key,
cat_name varchar(20)
)
go
insert into ……

以上方法都是測試過的
請認真看大家的回復，在12樓和4樓也貼了方法

#20

引用 18 楼 creatdatabase 的回复:

引用 12 楼 roy_88 的回复:

非要這樣做，可以利用null的特點處理

SQL code
create table cat_tb
(
cat_id int primary key,
cat_name varchar(20)
)
go
insert into cat_tb values(100,'数码电子')
insert into cat_tb values(……

檢查你執行的觸發器,是否與12樓方法一致

#21

引用 20 楼 roy_88 的回复:

引用 18 楼 creatdatabase 的回复:

引用 12 楼 roy_88 的回复:

非要這樣做，可以利用null的特點處理

SQL code
create table cat_tb
(
cat_id int primary key,
cat_name varchar(20)
)
go
insert into cat_tb values(100,'数码电子……

问题已解决了,多谢各位

#22

这种运用应该使用instead of 触发器吧，这样可以在判断约束之前执行触发器！

#23

这种情况，因为关系到两个表之间的约束，使用instead of触发器可以在判断约束之前就填充inserted和deleted表，执行完T-SQL后才判断约束。因此你的触发器中应当考虑在两个表中的更新和删除，当你修改之后，自然是符合约束关系的，也就不会报错了！

从性能上来说，触发器是没有约束效率的，我建议你使用约束的级联功能

智能推荐

注意！

本站转载的文章为个人学习借鉴使用，本站对版权不负任何法律责任。如果侵犯了您的隐私权益，请联系我们删除。

猜您在找

SqlServer级联删除两种实现方法：触发器，外键 SQL Server外键级联删除时触发器不能正常工作原因多表关联删除或修改记录是用外键好还是用触发器好 Sql Server 2000如何在不删除触发器的情况下，禁用触发器？谢谢！ Mysql学习笔记（八）由触发器回顾外键约束中的级联选项

智能推荐

在有外键的情况下,怎么用触发器级联删除\修改

23 个解决方案

#1

#2

#3

#4

#5

#6

#7

#8

#9

#10

#11

#12

#13

#14

#15

#16

#17

#18

#19

#20

#21

#22

#23

注意！

赞助商广告