当前位置： 移动技术网 > IT编程>数据库>MSSQL > SqlServer实现类似Oracle的before触发器示例

SqlServer实现类似Oracle的before触发器示例

2017年12月12日  | 移动技术网IT编程  | 我要评论

沈丘杀人案,於书凝,康洁 邓楠

1. 插入数据前判断数据是否存在

set ansi_nulls on 
go 
set quoted_identifier on 
go 
-- ============================================= 
-- author: <author,,name> 
-- create date: <create date,,> 
-- description: <description,,> 
-- ============================================= 
alter trigger categoryexisttrigger 
on productcategory 
instead of insert 
as 

declare @categoryname varchar(50); 
begin 
-- set nocount on added to prevent extra result sets from 
-- interfering with select statements. 
set nocount on; 

-- insert statements for trigger here 
select @categoryname = categoryname from inserted; 
if exists(select * from productcategory where categoryname =@categoryname) 
begin 
print 'category exists..' 
end; 
else 
begin 
insert into productcategory select * from inserted; 
end; 

end

2. 删除表中数据时需要先删除外键表的数据

set ansi_nulls on 
go 
set quoted_identifier on 
go 
-- ============================================= 
-- author: <author,,name> 
-- create date: <create date,,> 
-- description: <description,,> 
-- ============================================= 
alter trigger deleteordertrigger 
on orderheader 
instead of delete 
as 
declare @orderid varchar(50); 
begin 

set nocount on; 
select @orderid = orderid from deleted; 
delete from orderline where orderid = @orderid; 

end 
go

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网