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

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