CREATE TRIGGER [HumanResources].[dEmployee] 
   ON [HumanResources].[Employee] 
   INSTEAD OF DELETE --優先于DML觸發器執行
   NOT FOR REPLICATION --在復制代理執行插入、更新或刪除操作時，不進行觸發器約束
AS 
BEGIN
   DECLARE @Count int;
   SET @Count = @@ROWCOUNT;
   IF @Count = 0 
      RETURN;
   SET NOCOUNT ON;
   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;

CREATE TRIGGER dDatabaseLog
   ON DatabaseLog
   FOR DELETE
AS
BEGIN
   RAISERROR('不允許刪除DatabaseLog表內的數據', 11, 1)
   ROLLBACK
END

CREATE TRIGGER [safety]
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS
BEGIN
   RAISERROR('目前數據庫已禁止刪除,修改表', 11, 1)
   ROLLBACK;
END

CREATE TRIGGER [dbo].[dDatabaseLog_1]
   ON [dbo].[DatabaseLog]
   INSTEAD OF DELETE
AS
BEGIN
   IF EXISTS(
         SELECT * FROM DELETED WHERE DatabaseLogID < 1500
      )
   BEGIN
      --這里并不會調用FOR DELETE觸發器,因為這個觸發器是INSTEAD OF
      RAISERROR('INSTEAD OF觸發器禁止刪除DatabaseLogID < 1500的記錄', 11, 1)
      IF @@TRANCOUNT > 0
      BEGIN
         ROLLBACK TRANSACTION;
      END
   END
   ELSE
   BEGIN
      --這里開始才調用FOR DELETE觸發器
      DELETE FROM DatabaseLog WHERE DatabaseLogID IN (
         SELECT DatabaseLogID FROM DELETED
      )
   END
END

CREATE TRIGGER [dbo].[dDatabaseLog_2]
   ON [dbo].[DatabaseLog]
   FOR DELETE
AS
BEGIN
   --如果進入到這里的話,已證明INSTEAD OF DELETE觸發器已通過約束檢測
   IF EXISTS(
         SELECT * FROM DELETED WHERE DatabaseLogID < 1700
      )
   BEGIN 
      RAISERROR(
         'FOR觸發器禁止刪除DatabaseLogID < 1700的記錄',
         11, 1
      )
      --此處的回滾操作是由FOR DELETE發出
      ROLLBACK
   END
END

--切換數據庫
--use AdventureWorks

WITH cte_AllTriggers AS (
   SELECT 
      o.name TableName,          --觸發器所屬的對象(表 | 視圖)
      o.type_desc TableType,     --描述此觸發器所屬的對象是表還是視圖
      t.name TriggerName,        --觸發器的名稱
      t.object_id,               --觸發器的object_id
      t.parent_class_desc,       --觸發器是DML觸發器還是DDL觸發器
      t.type_desc,               --觸發器是SQL觸發器還是CLR觸發器
      t.create_date,             --觸發器的創建日期
      t.modify_date,             --上次使用 ALTER 語句修改觸發器的日期
      t.is_ms_shipped,           --是否為內部 SQL Server 組件代表用戶創建的觸發器
      t.is_disabled,             --觸發器是否被禁用
      t.is_not_for_replication,  --觸發器是否作為 NOT FOR REPLICATION 創建的
      t.is_instead_of_trigger    --1 = INSTEAD OF 觸發器, 0 = AFTER 觸發器
   FROM 
      sys.triggers t INNER JOIN sys.objects o 
         ON t.parent_id = o.object_id 
   UNION ALL
   SELECT 
      NULL TableName,          
      NULL TableType,     
      t.name TriggerName,        
      t.object_id,               
      t.parent_class_desc,       
      t.type_desc,               
      t.create_date,             
      t.modify_date,             
      t.is_ms_shipped,           
      t.is_disabled,             
      t.is_not_for_replication,  
      t.is_instead_of_trigger    
   FROM 
      sys.triggers t 
   WHERE 
      t.parent_id = 0
)
SELECT * FROM cte_AllTriggers ORDER BY TableName, TriggerName

ALTER TRIGGER [Sales].[iuIndividual] 
   ON [Sales].[Individual] --該觸發器是給[Sales].[Individual]定義的
   AFTER INSERT, UPDATE --是AFTER INSERT觸發器的同時,也是AFTERC觸發器
   NOT FOR REPLICATION
AS 
BEGIN
   DECLARE @Count int;
   SET @Count = @@ROWCOUNT;
   IF @Count = 0 
      RETURN; --如果插入刪除所影響的行為0時退出
   SET NOCOUNT ON;
   
   IF EXISTS (
         SELECT * FROM 
            inserted INNER JOIN [Sales].[Store] 
               ON inserted.[CustomerID] = [Sales].[Store].[CustomerID]
      ) 
   BEGIN
      IF @@TRANCOUNT > 0
      BEGIN
         --當插入[Sales].[Individual]表客戶Id已存在于[Sales].[Store]表時回滾
         ROLLBACK TRANSACTION;
      END
   END;
   
   
   IF UPDATE([CustomerID]) OR UPDATE([Demographics]) --如果CustomerID列或Demographics列更新|插入成功
   BEGIN
      --根據條件更新Demographics列
      UPDATE [Sales].[Individual] SET 
         [Sales].[Individual].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>0.00</TotalPurchaseYTD>
</IndividualSurvey>' 
      FROM inserted --連接到插入或更新的數據表
      WHERE 
         [Sales].[Individual].[CustomerID] = inserted.[CustomerID] 
         AND inserted.[Demographics] IS NULL; --將要更新或插入的Demographics為空時

      --根據條件插入一個XML節點
      UPDATE [Sales].[Individual] SET 
         [Demographics].modify(
            N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD> 
as first 
into (/IndividualSurvey)[1]') 
      FROM inserted 
      WHERE 
         [Sales].[Individual].[CustomerID] = inserted.[CustomerID] 
         AND inserted.[Demographics] IS NOT NULL 
         AND inserted.[Demographics].exist(N'declare default element namespace 
      "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
/IndividualSurvey/TotalPurchaseYTD') <> 1;
   END;
END;

ALTER TRIGGER [Production].[iWorkOrder] 
   ON [Production].[WorkOrder] 
   AFTER INSERT --AFTER插入觸發器
AS 
BEGIN
   DECLARE @Count int;
   SET @Count = @@ROWCOUNT;
   IF @Count = 0 
      RETURN; --插入的行為0時退出
   
   SET NOCOUNT ON;
   BEGIN TRY
      --往工作訂單表(TransactionHistory)插入生產工作訂單(WorkOrder)記錄
      INSERT INTO [Production].[TransactionHistory] (
         [ProductID]
         ,[ReferenceOrderID]
         ,[TransactionType]
         ,[TransactionDate]
         ,[Quantity]
         ,[ActualCost]
      )
      SELECT 
         inserted.[ProductID]
         ,inserted.[WorkOrderID]
         ,'W' --代表工作訂單
         ,GETDATE()
         ,inserted.[OrderQty]
         ,0
      FROM inserted;
   END TRY
   BEGIN CATCH
      --在Message窗口中輸出錯誤信息
      EXECUTE [dbo].[uspPrintError];
      IF @@TRANCOUNT > 0
      BEGIN
        ROLLBACK TRANSACTION; --事務數 > 0時回滾
      END
      
      --記錄錯誤信息進日志
      EXECUTE [dbo].[uspLogError];
   END CATCH;
END;