欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-02 09:34:52
1. 插入数据前判断数据是否存在 set ansi_nulls on go set quoted_identifier on go -- ====...

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