SQL Server Triggers Interview Questions and Answers
In this article, I am going to discuss the most frequently asked SQL Server Triggers Interview Questions and Answers. Please read our previous article where we discussed the most frequently asked SQL Server Indexes Interview Questions with Answers. Triggers are one of the most important concepts in SQL Server. As part of this article, we are going to discuss the following SQL Server Triggers Interview Questions with answers.
- What is a Trigger in SQL Server?
- What are the types of Triggers available in SQL Server?
- What are the special tables used by Triggers in SQL Server?
- What is MAGIC TABLE in triggers?
- What is the Inserted Magic Table?
- What is Deleted Magic Table in SQL Server?
- What is a DML Trigger in SQL Server?
- How to view the updating data in a table?
- What are DDL triggers in SQL Server?
- What is the use of DDL triggers in SQL Server?
- How to Enable, Disable and Drop Triggers 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 causes another trigger to be fired is called the nested trigger.
What are the types of Triggers available 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 is created inside of a trigger when we perform insert, update and delete operations. The Magic tables are invisible tables or virtual tables. We can see them only with the help of TRIGGERS in SQL Server. The Magic tables are those tables that 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. The UPDATE can be performed with the help of these two.
What is the 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 in SQL Server?
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 a DML Trigger in SQL Server?
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.
DML Triggers can be used to enforce business rules and data integrity. DML triggers are similar to constraints in the way they enforce integrity.
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 the other hand, 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 tables 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 in SQL Server?
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. The 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 in SQL Server?
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 in SQL Server?
To disable the trigger:
- Right-click on the trigger in object explorer and select “Disable” from the context menu
- We can also disable the trigger using the following T-SQL command: DISABLE TRIGGER trMyFirstTrigger ON DATABASE
To enable the trigger:
- Right-click on the trigger in object explorer and select “Enable” from the context menu
- We can also enable the trigger using the following T-SQL command: ENABLE TRIGGER trMyFirstTrigger ON DATABASE
To drop trigger:
- Right-click on the trigger in object explorer and select “Delete” from the context menu
- We can also drop the trigger using the following T-SQL command: DROP TRIGGER trMyFirstTrigger ON DATABASE
In the next article, I am going to discuss the most frequently asked SQL Server Views Interview Questions and Answers. Here, 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 needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.