SqlServer實現類似Oracle的before觸發器示例，sqlserveroracle

SqlServer實現類似Oracle的before觸發器示例，sqlserveroracle

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

SQL server觸發器在觸發前的問題

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
/*
上面觸發器在刪除users表內容前引發,但相應的操作並不被執行(就是說users表的內容不會刪除)，而運行的僅是觸發器SQL 語句本身,deleted和inserted都有響應*/
 

SQLServer觸發器的問題

careate trigger trigger_name on B
for insert
as
declare @a varchar(20),
seelct @a=name from inserted //注意：inserted、deleted是SQLServer觸發器特殊的表
if not exists（ select name from A where name=@a）
begin
rollback transaction
else
commit
end
go