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

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
before触发器
mssql 触发器、mssql 触发器 update、mssql 更新触发器、mssql 删除触发器、mssql 触发器实例，以便于您获取更多的相关知识。