--------------------------已签订单的产品停产------------------------------------
use northwind
create proc p1 @delid int
as
begin
delete from products where productid=@delid
end
--从产品表中删除一个产品
create trigger product_del
on products instead of delete
as
begin
declare @pid int
select * into #del from deleted
declare cr cursor scroll for select productid from #del
open cr
fetch last from cr into @pid
close cr
deallocate cr
delete from [order details] where productid=@pid
--删除该产品订单细节的相关记录
delete from products where productid=@pid
--从产品表中删除该产品记录
end
exec p1 1
--drop trigger product_del
select * from products
--------------------------------------------------我是分隔线,oh,yeah!!!!!!------------------------------------------------------------
create trigger ins_order_details
on [order details] instead of insert
as
begin
--删除该产品后的容错触发器,禁止添加与该产品有关的订单
declare @pid int
select * into #ins from inserted
declare cr cursor scroll for select productid from #ins
open cr
fetch last from cr into @pid
close cr
deallocate cr
select * from [order details] where productid=@pid
if @@rowcount=0
begin
print'该产品已经停产!'
rollback
end
else commit
end
--drop trigger ins_order_details
select * from [order details]
insert into [order details] select 11077,9,15,45,0
--有关删除记录的备份功能,参见本博客的SQL历史数据相关触发器示例
use northwind
create proc p1 @delid int
as
begin
delete from products where productid=@delid
end
--从产品表中删除一个产品
create trigger product_del
on products instead of delete
as
begin
declare @pid int
select * into #del from deleted
declare cr cursor scroll for select productid from #del
open cr
fetch last from cr into @pid
close cr
deallocate cr
delete from [order details] where productid=@pid
--删除该产品订单细节的相关记录
delete from products where productid=@pid
--从产品表中删除该产品记录
end
exec p1 1
--drop trigger product_del
select * from products
--------------------------------------------------我是分隔线,oh,yeah!!!!!!------------------------------------------------------------
create trigger ins_order_details
on [order details] instead of insert
as
begin
--删除该产品后的容错触发器,禁止添加与该产品有关的订单
declare @pid int
select * into #ins from inserted
declare cr cursor scroll for select productid from #ins
open cr
fetch last from cr into @pid
close cr
deallocate cr
select * from [order details] where productid=@pid
if @@rowcount=0
begin
print'该产品已经停产!'
rollback
end
else commit
end
--drop trigger ins_order_details
select * from [order details]
insert into [order details] select 11077,9,15,45,0
--有关删除记录的备份功能,参见本博客的SQL历史数据相关触发器示例