In this article, I am going to discuss most frequently asked SQL Server Triggers Interview Questions and Answers


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.

  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 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.

 

Syntax:

CREATE/ALTER TRIGGER<TRIGGER NAME>

ON<TABLE NAME> /<VIEW NAME>

[WITH<TRIGGER ATTRIBUTES>]

FOR/AFTER/INSTEADOF[INSERT, UPDATE, DELETE>]

AS

BEGIN

                <TRIGGER BODDY /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 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.

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 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:

  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 the 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

 

SQL Server Triggers Interview Questions and Answers article. I would like to have your feedback. Please post your feedback, question, or comments about this SQL Server Triggers Interview Questions and Answers article.