Deleting and Altering Triggers

• Deleting Table and View Triggers

        DROP TRIGGER PreventNewDiscounts;
  

• Deleting Database Triggers

        DROP TRIGGER PreventNewDiscounts;
  
        DROP TRIGGER PreventViewsModifications
        ON DATABASE:
  

• Deleting Server Triggers

        DROP TRIGGER PreventNewDiscounts;
  
        DROP TRIGGER PreventViewsModifications ON DATABASE:
  
        DROP TRIGGER DisallowLinkedServers ON ALL SERVER;
  

• Disabling Triggers

        DISABLE TRIGGER PreventNewDiscounts
        ON Discounts;
  
        DISABLE TRIGGER PreventViewsModifications
        ON DATABASE;
  
        DISABLE TRIGGER DisallowLinkedServers
        ON ALL SERVER;
  

• Enabling Triggers

        ENABLE TRIGGER PreventNewDiscounts
        ON Discounts;
  
        ENABLE TRIGGER PreventViewsModifications
        ON DATABASE;
  
        ENABLE TRIGGER DisallowLinkedServers
        ON ALL SERVER;
  

• Altering Triggers

        CREATE TRIGGER PreventDiscountsDelete
        ON Discounts
        INSTEAD OF DELETE
        AS  
            PRINT 'You are not allowed to data from the Discounts table.';
  
        DROP TRIGGER PrevetnDiscountsDelete;
          
  
        CREATE TRIGGER PreventDiscountsDelete
        ON Discounts
        INSTEAD OF DELETE
        AS
            PRINT "You are note allowed to remove data from the Discounts table."
  
        ALTER TRIGGER PreventDiscountsDelete
        ON Discounts
        INSTEAD OF DELETE
        AS 
            PRINT "You are not allowed to remove data from the Discounts table.';
  

---

Trigger Management

• Getting info from sys.triggers

        SELECT * FROM sys.triggers;
  

  

  name	trigger name
  object_id	unique identifier of the trigger
  parent_class	trigger type as integer
  parent_class_desc	trigger type as text
  parent_id	unique identifier of the parent object
  create_date	date of creation
  modify_date	date of last modification
  is_disabled	current state
  is_instead_of_trigger	INSTEAD OF or AFTER trigger

• Getting info from sys.server_triggers

        SELECT * FROM sys.server_triggers;
  

• Getting info from sys.trigger_events

        SELECT * FROM sys.trigger_events;
  

  

  object_id	unique identifier of the trigger
  type	event type as integer
  type_desc	event type as text
  event_group_type	event group type as integer
  event_group_type_desc	event group type as text

• Getting info from sys.server_trigger_events

        SELECT * FROM sys.server_trigger_events;
  

---

Troubleshooting triggers

• Tracking Trigger Executinos (system view)

        SELECT * FROM sys.dm_exec_trigger_stats;
  

• Tracking Trigger Executions (custom solution)

        CREATE TRIGGER PreventOrdersUpdate
        ON Orders
        INSTEAD OF UPDATE
        AS 
            RAISERROR('Updates on "Orders" table are not permitted. Place a new order to add new products.', 16, 1);
  

• Identifying triggers attached to a table

        SELECT name AS TableName,
                object_id AS TableId
        FROM sys.objects
        WHERE name = "Products";
  

        SELECT o.name AS TableName,
            o.object_id AS TableID,
            t.name AS TriggerName,
            t.object_id AS TriggerID,
            t.is_disabled AS IsDisabled,
            t.is_instead_of_trigger AS IsInsteadOf
        FROM sys.objects AS o
        INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
        WHERE o.name = 'Products';
  

• Identifying events capable of firing a trigger

            SELECT o.name AS TableName,
                o.object_id AS TableID,
                t.name AS TriggerName,
                t.object_id AS TriggerID,
                t.is_disabled AS IsDisabled,
                t.is_instead_of_trigger AS IsInsteadOf,
                te.type_desc AS FiringEvent
            FROM sys.objects AS o
            INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
            INNER JOIN sys.trigger_events AS te ON t.object_id = te.object_id
            WHERE o.name = 'Products';
  

• Viewing the trigger definitions

        SELECT o.name AS TableName,
            o.object_id AS TableID,
            t.name AS TriggerName,
            t.object_id AS TriggerID,
            t.is_disabled AS IsDisabled,
            t.is_instead_of_trigger AS IsInsteadOf,
            te.type_desc AS FiringEvent,
            OBJECT_DEFINITION(t.object_id) AS TriggerDefinition
        FROM sys.objects AS o
        INNER JOIN sys.triggers AS t ON t.parent_id = o.object_id
        INNER JOIN sys.trigger_events AS te ON t.object_id = te.object_id
        WHERE o.name = 'Products';