Sqlserver implements a before trigger example similar to Oracle

Time:2021-10-7

1. Before inserting data, judge whether the data exists


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 [email protected]) 
begin 
print 'Category exists..' 
end; 
else 
begin 
insert into ProductCategory select * from inserted; 
end; 

END

2. When deleting the data in the table, you need to delete the data in the foreign key table first


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