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

Diane ·

更新时间:2024-11-13

· 615 次阅读

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

您可能感兴趣的文章:oracle 触发器学习笔记Oracle触发器用法实例详解Oracle创建主键自增表(sql语句实现)及触发器应用oracle监控某表变动触发器例子(监控增,删,改)[Oracle] 如何使用触发器实现IP限制用户登录oracle 存储过程和触发器复制数据Oracle 触发器的使用小结ORACLE PL/SQL 触发器编程篇介绍Oracle触发器trigger详解Oracle中触发器示例详解

BEFORE 示例 sqlserver Oracle

1024 个赞