插入
USE [AdventureWorks]
GO
CREATE TRIGGER [Sales].[]iStore].[Store]
AFTER INSERT AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
--判断是否插入记录的CustomerID字段是否已经在[Sales].[Individua]表中存在相同的记录
IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[individua]
ON inserted.[CustomerID] = [Sales].[individua].[CustomerID])
BEGIN
--若有相同记录,则回滚事物
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspPrintError];
--调用存储过程,在错误日志表中记录错误产生详情
IF @@TRANSCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspPrintError];
END CATCH;
END ;
GO
删除
GO
CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResouces].[Employee]
INSTEAD OF DELETE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeleteCount int;
SELECT @DeleteCount = COUNT(*) FROM deleted;
IF @DeleteCount > 0
BEGIN
RAISERROR
(N'Employees cannot be deleted .They can only be marked as not current .',--message
10,--severity.
1);--State.
--回滚事物
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END;
END;
GO