不允许插入、删除某些表数据的触发器

插入

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

删除

USE [AdventureWorks]
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