程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> SqlServer完成相似Oracle的before觸發器示例

SqlServer完成相似Oracle的before觸發器示例

編輯:MSSQL

SqlServer完成相似Oracle的before觸發器示例。本站提示廣大學習愛好者:(SqlServer完成相似Oracle的before觸發器示例)文章只能為提供參考,不一定能成為您想要的結果。以下是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
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved