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

来源：动视网责编：小采时间：2020-11-09 07:04:48

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

SqlServer实现类似Oracle的before触发器示例:1. 插入数据前判断数据是否存在 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author，Name> -- Create date: <Crea

推荐度：

点击下载本文 文档为doc格式

导读SqlServer实现类似Oracle的before触发器示例:1. 插入数据前判断数据是否存在 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author，Name> -- Create date: <Crea

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