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.
- After Triggers (For Triggers): Fired after Insert, Update and Delete operations on a table.
- Instead of Triggers: Fired instead of Insert, Update and Delete operations on a table.
In SQL Server, there are 4 types of triggers
- DML Triggers – Data Manipulation Language
- DDL Triggers – Data Definition Language
- CLR triggers – Common Language Runtime
- 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.
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.
- After trigger (Sometimes called as FOR triggers)
- 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.
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
Fetch all the records from Employee table
SELECT * FROM Employee
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.
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
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.
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..
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.