Triggers in SQL server

A trigger in SQL is a special type of stored procedure that runs automatically when certain actions happen in a table or view. A trigger cannot be explicitly executed. Rather, a trigger is fired when a DML event occurs that the trigger is associated with, such as INSERT, UPDATE, or DELETE. Whenever the event takes place, the trigger fires and the trigger’s code runs.
SQL Server supports the association of triggers with two kinds of events:
1. Data manipulation events (DML triggers)
2. Data definition events (DDL triggers) such as CREATE, ALTER, DROP

SQL Server supports two kinds of DML triggers:
■ after This trigger fires after the event it is associated with finishes and can only be defined on permanent tables.
■ instead Of This trigger fires instead of the event it is associated with and can be defined on permanent tables and views. Instead of means Trigger fire before INSERT, UPDATE, DELETE.

IF OBJECT_ID('dbo.Tr_EmployeeDetailsDML', 'TR') IS NOT NULL
    DROP TRIGGER dbo.Tr_EmployeeDetailsDML
GO 
CREATE TRIGGER dbo.Tr_EmployeeDetailsDML
ON EmployeeDetails
AFTER DELETE, INSERT, UPDATE
AS
BEGIN
  IF @@ROWCOUNT = 0 RETURN;  
  SET NOCOUNT ON; 
  SELECT *  FROM Inserted; 
  SELECT *  FROM Deleted;
END;
 
go

delete from EmployeeDetails where EmployeeID = 3

The main purpose of this trigger is to give you feedback regarding how many rows are in the inserted and deleted tables.

IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL 
    DROP TRIGGER Production.tr_ProductionCategories_categoryname; 
GO 
CREATE TRIGGER Production.tr_ProductionCategories_categoryname 
ON Production.Categories 
AFTER INSERT, UPDATE, DELETE 
AS 
BEGIN 
  IF @@ROWCOUNT = 0 RETURN;  
  SET NOCOUNT ON; 
  IF EXISTS (SELECT COUNT(*) 
        FROM Inserted AS I 
        JOIN Production.Categories AS C 
          ON I.categoryname = C.categoryname 
        GROUP BY I.categoryname 
        HAVING COUNT(*) > 1 ) 
    BEGIN 
      THROW 50000, 'Duplicate category names not allowed', 0;     
    END
 
END; 
GO

 INSERT INTO Production.Categories (categoryname,description) 
     VALUES ('TestCategory1', 'Test1 description v1');

-- If condition not meet then insert data into table but if condition meet then show duplicate error. 

INSTEAD OF Triggers:
The INSTEAD OF trigger executes a batch of T-SQL code instead of the INSERT, UPDATE, or DELETE statement.
Although INSTEAD OF triggers can be created against both tables and views, they are commonly used with views.

IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL 
  DROP TRIGGER Production.tr_ProductionCategories_categoryname; 
GO 
CREATE TRIGGER Production.tr_ProductionCategories_categoryname 
ON Production.Categories 
INSTEAD OF INSERT, UPDATE, DELETE
AS 
BEGIN 
  SET NOCOUNT ON; 
  IF EXISTS (SELECT COUNT(*) 
        FROM Inserted AS I 
        JOIN Production.Categories AS C 
          ON I.categoryname = C.categoryname 
        GROUP BY I.categoryname 
        HAVING COUNT(*) > 1 )    BEGIN 
      THROW 50000, 'Duplicate category names not allowed', 0; 
    END; 

END; 
GO 

  INSERT INTO Production.Categories (categoryname,description) 
     VALUES ('TestCategory2', 'Test1 description v1');


-- If condition not meet then not data insert into table but if condition meet then show duplicate error. 

AFTER triggers execute after the DML operation is complete, whereas INSTEAD OF triggers execute before the DML operation and can replace the original operation with custom logic.

--If Insert data into table in case of INSTEAD OF

IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL 
  DROP TRIGGER Production.tr_ProductionCategories_categoryname; 
GO 
CREATE TRIGGER Production.tr_ProductionCategories_categoryname 
ON Production.Categories 
INSTEAD OF INSERT, UPDATE, DELETE
AS 
BEGIN 
  SET NOCOUNT ON; 
  IF EXISTS (SELECT COUNT(*) 
        FROM Inserted AS I 
        JOIN Production.Categories AS C 
          ON I.categoryname = C.categoryname 
        GROUP BY I.categoryname 
        HAVING COUNT(*) > 1 )    BEGIN 
      THROW 50000, 'Duplicate category names not allowed', 0; 
    END; 
    ELSE 
      INSERT INTO Production.Categories (categoryname,description) 
      SELECT categoryname,description FROM Inserted

END; 
GO 

  INSERT INTO Production.Categories (categoryname,description) 
     VALUES ('TestCategory2', 'Test1 description v1');


-- If condition not meet then not data insert into table but if condition meet then show duplicate error. 

Here are some interview questions about SQL Server triggers, along with their answers:

Basic Questions

1. What is a trigger in SQL Server?
   • A trigger is a special kind of stored procedure that automatically executes when specific actions (like INSERT, UPDATE, or DELETE) occur on a table or view.
2. What are the types of triggers in SQL Server?
   • DML Triggers: Work with data manipulation operations (INSERT, UPDATE, DELETE).
   • DDL Triggers: Work with data definition operations (CREATE, ALTER, DROP).
   • Logon Triggers: Run in response to a user logging into the database.
3. What are the two types of DML triggers?
   • AFTER Trigger: Executes after the DML operation is successfully completed.
   • INSTEAD OF Trigger: Executes instead of the DML operation.
4. What is the difference between AFTER and INSTEAD OF triggers?
   • AFTER Trigger runs after the DML operation completes.
   • INSTEAD OF Trigger replaces the DML operation and executes custom logic instead.
5. Can triggers be manually executed?
   • No, triggers cannot be called directly. They are automatically executed when their associated event occurs.

Intermediate Questions

6. Where are AFTER triggers used?
   • AFTER triggers are used to enforce additional constraints, audit changes, or log modifications after the DML operation.
7. Can we define multiple triggers for the same event on a table?
   • Yes, multiple triggers can be defined for the same event. However, the execution order is not guaranteed unless explicitly specified.
8. What is the purpose of the Inserted and Deleted tables in triggers?
   • These are temporary tables used inside triggers:
     • Inserted: Holds new rows for INSERT or UPDATE.
     • Deleted: Holds old rows for DELETE or UPDATE.
9. Can an INSTEAD OF trigger be defined on a view?
   • Yes, INSTEAD OF triggers are often used on views to enable complex logic during INSERT, UPDATE, or DELETE operations.
10. What happens if an AFTER trigger fails?
    • If an AFTER trigger fails, the entire transaction that fired the trigger is rolled back.

Advanced Questions

11. How can you control the order of execution for multiple triggers on the same table?
    • Use the sp_settriggerorder stored procedure to specify the first and last triggers.
12. Can a trigger call another trigger?
    • Yes, triggers can cause other triggers to fire. This is called nested triggers. However, the nesting level is limited to 32.
13. What is recursion in triggers, and how can it be controlled?
    • Recursion occurs when a trigger indirectly or directly calls itself. It can be controlled using the RECURSIVE_TRIGGERS database setting.
14. How can you disable or enable a trigger in SQL Server?
    • To disable a trigger: DISABLE TRIGGER trigger_name ON table_name;
    • To enable a trigger: ENABLE TRIGGER trigger_name ON table_name;
15. How can you prevent a trigger from firing?
    • Use the DISABLE TRIGGER command to temporarily disable the trigger.

Scenario-Based Questions

16. Write a trigger to prevent the deletion of rows from a table.

CREATE TRIGGER trgPreventDelete
ON Employees
INSTEAD OF DELETE
AS
BEGIN
    PRINT 'Deletion is not allowed in this table';
END;

17. How would you handle duplicate records using a trigger?

• Use an INSTEAD OF trigger to check for duplicates before performing an INSERT:

CREATE TRIGGER trgPreventDuplicates
ON Employees
INSTEAD OF INSERT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Inserted I JOIN Employees E ON I.EmployeeID = E.EmployeeID)
    BEGIN
        THROW 50000, 'Duplicate EmployeeID is not allowed', 0;
    END
    ELSE
    BEGIN
        INSERT INTO Employees (EmployeeID, Name)
        SELECT EmployeeID, Name FROM Inserted;
    END;
END;

18. What are recursive triggers? Can you enable or disable recursion?

• Recursive triggers occur when a trigger calls itself directly or indirectly.
• Enable or disable recursion using: ALTER DATABASE dbname SET RECURSIVE_TRIGGERS ON;

19. What are some best practices for using triggers?

• Avoid using triggers for simple validations; use constraints instead.
• Keep trigger logic simple and optimized to avoid performance issues.
• Use triggers only when necessary to automate complex tasks.

20. How would you debug a trigger in SQL Server?

• Use PRINT statements to display values during trigger execution.
• Query the Inserted and Deleted tables to verify the data.
• Use SQL Profiler to trace trigger execution.

Quick Quiz

• True or False:
  • INSTEAD OF triggers can run after the DML operation. (False)
  • AFTER triggers can be defined on views. (False)
  • Inserted and Deleted tables are available in both AFTER and INSTEAD OF triggers. (True)