触发器的定义——解决主外键约束与触发器冲突问题

触发器的定义（sql server2008）

解决主外键约束与触发器冲突问题

实验题目

题目六：
药品（编号，名称，价格，厂商）
处方（药品编号，数量，医生编号）
医生 （编号，姓名，科室，职称）
建立药品的delete 触发器，只有当此药品品没有人买时才可删除，若有处方记录不能删除。

插入的数据

insert into medicine (mno,mname,price,industno)values(1,'阿司匹林',5.5,'北方制药');
insert into medicine (mno,mname,price,industno)values(2,'头孢拉定片',7.5,'北方制药');
insert into medicine (mno,mname,price,industno)values(3,'红霉素',11.5,'南方制药');
insert into medicine (mno,mname,price,industno)values(4,'吗丁啉',21.5,'南方制药');
insert into medicine (mno,mname,price,industno)values(5,'蛇胆川贝液',15,'汉中制药');
insert into medicine (mno,mname,price,industno)values(6,'六味地黄丸',30,'华北制药');
insert into medicine (mno,mname,price,industno)values(7,'尼美舒利颗粒',6,'北方制药');
insert into medicine (mno,mname,price,industno)values(8,'皮炎平',10,'青藏制药');
insert into doctor(docno,dname,room,position)values(2012001,'张力','口腔科','医师');
insert into doctor(docno,dname,room,position)values(2012002,'李敏','眼科','主治医师');
insert into doctor(docno,dname,room,position)values(2012003,'王杰','儿科','医师');
insert into doctor(docno,dname,room,position)values(2012004,'陈超','呼吸科','医师');
insert into doctor(docno,dname,room,position)values(2012005,'李勇','皮肤科','医师');
insert into prescription(mno,number,docno)values(1,18,2012001);
insert into prescription(mno,number,docno)values(1,2,2012002);
insert into prescription(mno,number,docno)values(2,10,2012001);
insert into prescription(mno,number,docno)values(2,31,2012001);
insert into prescription(mno,number,docno)values(3,14,2012003);
insert into prescription(mno,number,docno)values(4,17,2012003);
insert into prescription(mno,number,docno)values(4,4,2012004);
insert into prescription(mno,number,docno)values(5,10,2012001);
insert into prescription(mno,number,docno)values(6,10,2012001);
insert into prescription(mno,number,docno)values(6,3,2012004);
insert into prescription(mno,number,docno)values(6,4,2012001);

首先，我的触发器是这样定义的

go
create trigger delete_medicine on medicine
for delete
as
if exists(
select mno from deleted
where mno in(
select mno from prescription
)
)
begin
print '不能被删除'
end
go

但是，当我执行删除操作时

delete from medicine where mno=1

显然，触发器与外键约束发生冲突，那怎么办嘛
经过多次尝试，还是没有结果，于是，我借鉴了一篇也是CSDN上的文章，链接如下
链接: link.
我把for改成了instead of 这样在删除之前激发触发器，就不会和外键约束发生冲突，但如果这样做，就必须在后面写上手动手动删除语句，具体代码如下：

go
alter trigger delete_medicine on medicine
instead of delete
as
declare @mno int
select @mno=mno from deleted
if exists(
select mno from deleted
where mno in(
select mno from prescription
)
)
begin
print '不能被删除'
end
else
begin
delete from medicine where mno=@mno
select *from deleted
end
go

这样，当删除有使用记录的药品结果如下

delete from medicine where mno=1

而删除没有使用记录的药品，再查询药品表，发现成功！

delete from medicine where mno=8

真是翻身农奴把歌唱，好开心！感谢CSDN各位大佬们！