300+ [UPDATED] Trigger Interview Questions

1. Explain Trigger And Trigger Types?

A trigger is a special kind of a store procedure that executes in response to certain action performed on the table like insertion, deletion or updating of data

Basically there are two logical types:

• DDL triggers
• DML triggers

DDL triggers you can categorize:

• Transact-SQL DDL Trigger
• CLR DDL Trigger

DML trigger types are:

• INSTEAD OF DML trigger
• AFTER DML trigger

There is one special type trigger called logon trigger in sql server.

2. What Are Ddl Triggers And Types Of Ddl Trigger?

DDL triggers fire in response to an event happening on the server. However, DDL triggers do not fire in response to UPDATE, INSERT, or DELETE (DML) statements on a table or view. Instead, they fire in response to Data Definition Language (DDL) statements that start with the keywords CREATE, ALTER, and DROP.

DDL triggers you can categorize:

• Transact-SQL DDL Trigger
• CLR DDL Trigger

3. What Are Dml Triggers And Types Of Dml Triggers?

DDL triggers fire in response to UPDATE, INSERT, or DELETE (DML) statements on a table or view.

DML trigger types are:

• INSTEAD OF DML trigger
• AFTER DML trigger

4. What Is Transact-sql Ddl Trigger?

A special type of Transact-SQL stored procedure that executes one or more Transact-SQL statements in response to a server-scoped or database-scoped event. For example, a DDL Trigger may fire if a statement such as ALTER SERVER CONFIGURATION is executed or if a table is deleted by using DROP TABLE.

5. What Is Clr Ddl Trigger?

Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.

Example:

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

   PRINT ‘You must disable Trigger “safety” to drop or

alter tables!’

   ROLLBACK;

6. What Is After Dml Trigger?

AFTER triggers are executed after the action of the INSERT, UPDATE, MERGE, or DELETE statement is performed successfully.

7. What Is Instead Of Dml Trigger?

DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. Therefore, they can be used to perform error or value checking on one or more columns and the perform additional actions before insert, updating or deleting the row or rows.

8. What Is Logon Trigger?

Special type of trigger fire when LOGON event of Sql Server is raised is called lo logon trigger. You can use this trigger to audit Sql server activities, control server sessions, such as to track login activity or limit the number of sessions for a specific login.

9. Why The Trigger Fires Multiple Times In Single Login?

Reason multiple SQL Server services are running as well as intelligence is turned on.

10. It Is Possible To Create Trigger On Views?

Yes, only Instead Of trigger.

11. How Many Triggers Are Possible Per Table?

One Instead Of Trigger and any After Triggers.

12. When Multiple After Triggers Are Attached To Sql Table, How To Control The Order Of Execution?

Using sp_settriggerorder procedure.

13. What Is The Difference Between For Trigger And After Trigger?

It’s same.

14. What Are The Types Of Triggers?

It’s Instead Of trigger and For Trigger.

15. What Is The Difference Between Instead Of And After/for Trigger?

Instead Of trigger will be fired on behalf of DML statements whereas After trigger will be fired after DML statements.

16. Is It Possible To Create Trigger On Views?

Yes, only Instead Of trigger.