SQL Server Triggers Interview Questions

SQL Server Triggers Interview Questions and Answers

In this article, I will discuss most frequently asked SQL Server Triggers interview questions and answers. Triggers are one of the most important concepts in SQL Server.

What is a Trigger in SQL Server?

A Trigger is a database object which can also be treated as a special kind of stored procedure that automatically executes when language events (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are similar to stored procedures in that both consist of procedural logic that is stored at the database level.  

However, the difference between a trigger and a stored procedure is that the trigger is attached to a table and is only fired when an INSERT, UPDATE or DELETE operation occurred whereas stored procedures are not attached to a specific table and can be executed explicitly by making a call to the stored procedure. In addition to that, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself; so, when the trigger is fired because of data modification, it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself which cause another trigger to be fired is called as the nested trigger.

What are the two types of Triggers in SQL Server?

The following are the two types of triggers in SQL Server.

  1. After Triggers (For Triggers): Fired after Insert, Update and Delete operations on a table.
  2. Instead of Triggers: Fired instead of Insert, Update and Delete operations on a table.

In SQL Server, there are 4 types of triggers 

  1. DML Triggers – Data Manipulation Language
  2. DDL Triggers – Data Definition Language
  3. CLR triggers – Common Language Runtime
  4. Logon triggers
What are the special tables used by Triggers in SQL Server?

Triggers make use of two special tables called inserted and deleted. The inserted table contains the data referenced in an INSERT before it is committed to the database. The deleted table contains the data in the underlying table referenced in a DELETE before it is removed from the database. When an UPDATE is issued both tables are used. More specifically, the new data referenced in the UPDATE statement is contained in the inserted table and the data that is being updated is contained in the deleted table.

What is MAGIC TABLE in triggers?

These are the special kind of table which are created inside of a trigger when we perform insert, update and delete operations.

Magic tables are invisible tables or virtual tables. We can see them only with the help of TRIGGERS in SQL Server.

Magic tables are those tables which allow us to hold INSERTED, DELETED and UPDATED values during insert delete and update DML operations on a table in SQL Server.

Basically there are two types of magic tables in SQL Server namely INSERTED and DELETED magic table.

UPDATE can be performed with the help of these two.

What is INSERTED Magic Table?

This table is created when we perform an insert operation that provides access to the values being inserted into the table.

Whenever we insert the values into a table those values we can see in the inserted magic table like below

CREATE TRIGGER T1 ON EMPLOYEE
FOR INSERT
AS
BEGIN
SELECT * FROM inserted
END

Let’s insert one record

INSERT INTO EMPLOYEE VALUES(106,’FF’, 30000, ‘UP’)

What is Deleted Magic Table?

This table is created when we perform a delete operation providing access to the record being deleted.

Whenever we delete a Record from a table the record information can view with the deleted magic table like below.

CREATE TRIGGER T2 ON EMPLOYEE
FOR DELETE
AS
BEGIN
SELECT * FROM deleted
END

Let’s delete one record

DELETE FROM EMPLOYEE WHERE EID = 105

What is DML Trigger?

DML stands for Data Manipulation Language. INSERT, UPDATE, and DELETE statements are DML statements. The DML triggers are fired whenever data is modified using INSERT, UPDATE, and DELETE events.

These DML Triggers execute when the user tries to modify or change data through data manipulation language events such as INSERT, UPDATE and DELETE statements on the table or view.

The DML Triggers can be used to enforce business rules and data integrity. DML triggers are similar to constraints in the way they enforce integrity.

Syntax:

CREATE/ALTER TRIGGER <TRIGGER NAME>
ON <TABLE NAME> /<VIEW NAME>
[WITH <TRIGGER ATTRIBUTES>]
FOR / AFTER/InsteadOf[<INSERT, UPDATE, DELETE>]
AS
BEGIN
     <TRIGGER BODY / STATEMENTS>
END

DML triggers can be again classified into 2 types.

  1. After trigger (Sometimes called as FOR triggers)
  2. Instead of trigger

After triggers as the name says fires after the triggering action. The INSERT, UPDATE, and DELETE statements cause an after trigger to fire after the respective statements complete execution.

On other hands, as the name says, INSTEAD of triggers, fires instead of the triggering action. The INSERT, UPDATE, and DELETE statements, can cause an INSTEAD OF trigger to fire INSTEAD OF the respective statement execution.

How to view the updating data in a table?

When we perform an update operation we will be having both inserted and deleted table where inserted will provide access to the new values being inserted and deleted table provides access to the old values of the table.

Whenever we update a record data in the table, we can view the new value in the inserted magic table and old value in the deleted magic table like below.

CREATE TRIGGER T3 ON EMPLOYEE
FOR UPDATE
AS
BEGIN
     SELECT * FROM deleted
     SELECt * FROM inserted
END

Let’s delete one record

UPDATE EMPLOYEE SET ENAME =’PRANAYA’ WHERE EID = 101

What are DDL triggers?

DDL triggers fire in response to DDL events – CREATE, ALTER, and DROP (Table, Function, Index, Stored Procedure etc…).

The DDL triggers fires in response to a variety of data definition language events such as Create, Alter, Drop, Grant, Denay and Revoke.

DDL triggers are introduced from SQL Server 2005 version which will be used to restrict DDL operations such as CREATE, ALTER and DROP commands.

A DDL trigger is a special type of stored procedure that executes in response to a server scoped or database scoped events.

DDL triggers fire only after the DDL statements execute so we cannot use “InsteadOf Triggers” here and moreover DDL triggers will not fire in response to events that affect local temporary tables.

Syntax:

CREATE TRIGGER <TRIGGER NAME>
ON ALL SERVER/ DATABASE
[with trigger attributes]
FOR / ALTER <Event_Type>
AS
BEGIN
     <TRIGGER BODY/ STATEMENTS>
END

<Event_Type> refers to the event that will fire the trigger which can be anything like Create_Table, Drop_Table, Alter_Table etc.

What is the use of DDL triggers?

If we want to execute some code in response to a specific DDL event

To prevent certain changes to our database schema

Audit the changes that the users are making to the database structure

How to Enable, Disable and Drop Triggers?

To disable trigger

1. Right click on the trigger in object explorer and select “Disable” from the context menu 

2. We can also disable the trigger using the following T-SQL command

DISABLE TRIGGER trMyFirstTrigger ON DATABASE

To enable trigger

1. Right click on the trigger in object explorer and select “Enable” from the context menu 

2. We can also enable the trigger using the following T-SQL command

ENABLE TRIGGER trMyFirstTrigger ON DATABASE

To drop trigger

1. Right click on the trigger in object explorer and select “Delete” from the context menu 

2. We can also drop the trigger using the following T-SQL command

DROP TRIGGER trMyFirstTrigger ON DATABASE

Give a real-time example of triggers usage?

It is recommended to avoid triggers in a real-time environment. There is one scenario I can think of why you may want to use triggers in a real-time environment. Let us use an example to understand this.

I have 2 tables, Employee, and Gender as shown below. GenderId is the foreign key in Employee table.

--Create Gender table
CREATE TABLE Gender
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  GenderName VARCHAR(50)
)
GO

-- Create Person table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  GenderID INT
)
GO

--Add foreign key for GenderID Column
ALTER TABLE Employee 
ADD CONSTRAINT Employee_GenderID_FK FOREIGN KEY (GenderID) 
REFERENCES Gender(ID)
GO

--Insert some test data to Gender table
INSERT INTO Gender VALUES('Male')
INSERT INTO Gender VALUES('Female')
GO

--Insert some test data into Person table
INSERT INTO Employee VALUES('PRANAYA',1)
INSERT INTO Employee VALUES('TARUN',1)
INSERT INTO Employee VALUES('PRIYANKA',2)
INSERT INTO Employee VALUES('PREETY',2)
INSERT INTO Employee VALUES('RAMESH',1)
INSERT INTO Employee VALUES('PRAMOD',1)
INSERT INTO Employee VALUES('ANURAG',1)
INSERT INTO Employee VALUES('HINA',2)
GO

-- Fetch all the records from Gender table
SELECT * FROM Gender

SQL Server Triggers interview questions and answers

Fetch all the records from Employee table

SELECT * FROM Employee

GO

SQL Server Triggers interview questions and answers

Now create a view based on the above two tables as shown below

CREATE VIEW vwEmployees
AS
SELECT E.ID, Name, G.GenderName as Gender
FROM Employee E
INNER JOIN Gender G
ON E.GenderID = G.ID

Select * from vwEmployees will give us the result as shown below.

SQL Server Triggers interview questions and answers

Now update the view the following query. This will change the Gender Text to Female in Gender table for Id = 1. This is not what we have expected.

Update vwEmployees Set Gender=’Female’ where Id=1

The base tables are updated incorrectly. So, Select * from vwEmployees will give us the result as shown below.SQL Server Triggers interview questions and answers

To update the base tables correctly, we can create an INSTEAD OF trigger on the view as shown below.

CREATE TRIGGER TR_INSTEAD_OF_UPDATE
ON vwEmployees INSTEAD OF UPDATE
AS
BEGIN
  DECLARE @EMP_ID INT
  DECLARE @GENDER_ID INT
  DECLARE @GENDER_TEXT VARCHAR(100)

  SELECT @GENDER_TEXT = Gender, @EMP_ID = ID FROM inserted
  SElECT @GENDER_ID = ID FROM Gender WHERE GenderName = @GENDER_TEXT

  UPDATE Employee SET GenderID = @GENDER_ID WHERE ID = @EMP_ID
END

Now run the query below which will update the underlying base tables correctly.

Update vwEmployees Set Gender=’Female’ where Id=1

Select * from vwEmployees will show the correct result set as shown below. 

SQL Server Triggers interview questions and answers

The INSTEAD OF trigger has correctly updated the GenderId in Employee table.

So, instead of triggers can be used to facilitate updating Views that are based on multiple base tables.

Actually triggers are great to use in real time….we use it in our production database on order table…for simplicity if we take your example then here you can put check in the trigger that if someone tries to change female person gender to male then u can reject that change via trigger….trigger is the closest thing to the database so if u want super tight control on the data then u should use trigger to handle the scenario..

SUMMARY

In this article, I try to explain most frequently asked SQL Server Triggers interview questions and answers. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Follow Us

Leave a Reply

Your email address will not be published. Required fields are marked *