Frequently asked 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
a. DDL triggers
b. DML triggers
DDL triggers you can categorize:
1. Transact-SQL DDL Trigger
2. CLR DDL Trigger
DML trigger types are:
1. INSTEAD OF DML trigger
2. 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:
1. Transact-SQL DDL Trigger
2. 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:
1. INSTEAD OF DML trigger
2. 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. Is it possible to create trigger on views?
Yes, only InsteadOf trigger.
11. How many triggers are possible per table?
One InsteadOfTrigger 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.
