SqlServer 类似Oracle的before触发器实现方法

这篇文章主要为大家详细介绍了SqlServer 类似Oracle的before触发器实现方法，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

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

注：关于SqlServer 类似Oracle的before触发器实现方法的内容就先介绍到这里，更多相关文章的可以留意