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

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

2020-04-02 15:29whsnow Sql Server

本节主要介绍了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