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
