SQL server implements a before trigger example similar to Oracle, sqlserveroracle

SQL server implements a before trigger example similar to Oracle, sqlserveroracle

1. Determine whether the data exists before inserting the data

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. When deleting data in a table, you must first Delete the data in the foreign key table.

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

SQL server trigger before triggering

Create trigger tri_deleteLevel on users
Instead of delete
As
Declare @ v_usid int
Select @ v_usid = usid from deleted
Delete level_assign where usid = @ v_usid
/*
The above trigger is triggered before deleting the users table content, but the corresponding operation is not executed (that is, the users table content is not deleted), and only the trigger SQL statement itself is run, both deleted and inserted have responses */

SQL Server trigger Problems

Careate trigger trigger_name on B
For insert
As
Declare @ a varchar (20 ),
Seelct @ a = name from inserted // Note: inserted and deleted are special tables of SQLServer triggers.
If not exists (select name from A where name = @)
Begin
Rollback transaction
Else
Commit
End
Go