15 Most Famous SQL Server Trigger's Interview Question

1. How to specify the Trigger orders in SQL Server?

If you are working with multiple triggers then you want to specify the order in which the trigger will fire. But in SQL server you have limitation for that, you can only specify the first and last trigger to execute.

The first and last trigger to execute can be specified using the sp_settriggerorder stored procedure. This provides some degree of control over the order in which multiple triggers execute, but it is important to understand that triggers other than the first and last are executed in an undefined order.

DML Triggers:

There can be only one First and one Last trigger for each statement on a single table.

If a First trigger is already defined on the table, database, or server, you cannot designate a new trigger as First for the same table, database, or server for the same statement_type. This restriction also applies Last triggers.

DDL Triggers:

If a DDL trigger with database scope and a DDL trigger with server scope exist on the same event, you can specify that both triggers be a First trigger or a Last trigger. However, server-scoped triggers always fire first. In general, the order of execution of DDL triggers that exist on the same event is as follows:

1.       The server-level trigger marked First.

2.       Other server-level triggers.

3.       The server-level trigger marked Last.

4.       The database-level trigger marked First.

5.       Other database-level triggers.

6.       The database-level trigger marked Last.

LOGON Triggers:

Multiple triggers can be defined on the LOGON event. Any one of these triggers can be designated the first or last trigger to be fired on an event by using the sp_settriggerorder system stored procedure. SQL Server does not guarantee the execution order of the remaining triggers.

Important:

If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset by using sp_settriggerorder.

Learn more on Trigger order with Replication.

Example:

--The following example specifies that trigger trgInsertMessage be the first trigger to fire after an INSERT operation occurs on the Employee table.

sp_settriggerorder @triggername= '[dbo].[trgInsertMessage]', @order='First', @stmttype = 'INSERT';

--if you want to make it last you can do it as follows:

sp_settriggerorder @triggername= '[dbo].[trgInsertMessage]', @order='Last', @stmttype = 'INSERT';

--For DDL Trigger

sp_settriggerorder @triggername= 'ddlDatabaseTriggerLog', @order='First', @stmttype = 'ALTER_TABLE', @namespace = 'DATABASE';

@namespace: When triggername is a DDL trigger, specifies whether triggername was created with database scope or server scope. If triggername is a logon trigger, SERVER must be specified. If not specified, or if NULL is specified, triggername is a DML trigger.

2. How to Enable / Disable the triggers in SQL Server?

Why we have to Enable / Disable a trigger? – Very simple, if you created some trigger for any table, and due to some region you don’t want to fire some of the trigger, so in place of delete, you can disable it and later as per requirement enable it.

Enabling a trigger does not re-create it. A disabled trigger still exists as an object in the current database, but does not fire. Enabling a trigger causes it to fire when any events on which it was originally programmed are executed.

Triggers are disabled by using DISABLE TRIGGER. DML triggers defined on tables can be disabled or enabled by using ALTER TABLE.

To enable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created.

To enable a DDL or logon trigger with server scope (ON ALL SERVER), a user must have CONTROL SERVER permission on the server.

To enable a DDL trigger with database scope (ON DATABASE), at a minimum, a user must have ALTER ANY DATABASE DDL TRIGGER permission in the current database.

-- Using Diabled command – DML Trigger

DISABLE TRIGGER dbo.MyTest1 ON dbo.NewContact;

--OR, use the ALTER TABLE DDL command with the ENABLE TRIGGER or DISABLE TRIGGER option:

ALTER TABLE dbo.Employee

ENABLE TRIGGER --or DISABLE TRIGGER

trgInsertMessage;

--Disabling a DDL trigger

DISABLE TRIGGER ProtectObjects ON DATABASE;

To view the enabled status of a trigger, use the OBJECTPROPERTY() function, passing to it the object ID of the trigger and the ExecIsTriggerDisabled option:

SELECT OBJECTPROPERTY(

       OBJECT_ID('dbo.trgInsertMessage'), 'ExecIsTriggerDisabled');

       -- o: enabled and 1: disabled

3. How to list all the Triggers?

The following query lists all the triggers in the database based on the sys.triggers catalog view. Contains a row for each object that is a trigger, with a type of TR or TA.

SELECT Sc.name + '.' + Ob.name as [table], Tr.name as [trigger],

       CASE (Tr.is_instead_of_trigger )

              WHEN 0 THEN 'after'

              WHEN 1 THEN 'instead of'

       END AS type,

       CASE (Tr.is_disabled)

              WHEN 0 THEN 'enabled'

              WHEN 1 THEN 'disabled'

       END AS status

FROM sys.triggers Tr

       JOIN sys.objects Ob

              ON Tr.parent_id = Ob.object_id

       JOIN sys.schemas Sc

              ON Ob.schema_id = Sc.schema_id

WHERE Tr.type = 'TR' and Tr.parent_class = 1

ORDER BY Sc.name + '.' + Ob.name, Tr.name

-- you can also use You can use syscomments and sysobjects to list the triggers

select xtype, object_Name(Parent_obj) Parent,

B.Name, text

from syscomments A

JOIN sysobjects B ON

A.id = B.id

Where xtype = 'TR'

4. How Triggers Execute with transactions in SQL Server?

DML Triggers execute in response to any data modifications so its affect the transactional state in which they're fired. Knowing these effects can prevent conflicts with constraints, locking, and blocking on the affected tables.

So when a modification occurs, every transaction invokes various checks in the following order:

1.       IDENTITY INSERT check.

2.       Nullability constraint.

3.       Data-type check.

4.       The statement is executed (INSERT, UPDATE, or DELETE).

5.       If an INSTEAD OF trigger is defined on the table, it executes in place of the actual modification.

Execution of DML will stop here.

6.       Any constraints on the table are checked. If constraints are violated, an error is returned and the process is stopped.

Like, Primary-key constraint, Check constraints, Foreign-key constraints and many more.

7.       DML execution and update to the transaction log.

8.       AFTER trigger execution:

The first and last trigger to fire can be specified; the rest of the AFTER triggers execute in an undefined order.

If a rollback transaction is encountered, all modifications must be rolled back.

9.       The transaction is committed.

A trigger executes once per INSERT, UPDATE, or DELETE statement even if multiple rows are modified. It is common for UPDATE and DELETE statements to affect multiple rows, but this is also possible with INSERT statements.

The inserted and deleted tables contain a copy of all affected rows.

5. What are Inserted and Deleted Tables in Trigger?

These two tables also known as magic tables of SQL Server.

When a DML trigger is executing, it has access to two memory-resident tables that allow access to the data that was modified: Inserted and Deleted. These tables are available only within the body of a trigger for read-only access, and they are automatically created and managed by SQL Server.

The structures of the inserted and deleted tables are the same as the structure of the table on which the trigger is defined.

For insert operations, all inserted rows are available in the inserted table.

For delete operations, all deleted rows are available in the deleted table.

For update operations, an update can be thought of as a delete followed by an insert. Both the inserted and deleted tables are available within an UPDATE trigger. The deleted table stores the records before modification (old values), and the inserted table stores records after they were updated (new values).

6. What are differences between DDL Triggers and DML Triggers?

In some ways, DDL triggers are similar to DML triggers, but there are some significant differences. Both types of triggers are executed in response to events, but the types of events are different. DML triggers execute in response to INSERT, UPDATE, and DELETE statements, where DDL triggers execute in response to DDL operations. DML triggers are scoped at the table level, where DDL triggers can be scoped at the database or server-instance level.

DML Triggers	DDL Triggers
Respond to INSERT, UPDATE, and DELETE statements	Respond to CREATE, ALTER, and DROP statements
Scoped per table	Scoped at database or server instance
Create inserted and deleted tables to give information  about the event that occurred	Don't create any tables. Information about the DDL operation is retrieved using the EVENTDATA() function
Can create INSTEAD OF and AFTER triggers	DDL triggers always execute after a DDL operation has occurred. You cannot create INSTEAD OF DDL triggers.

7. What are differences between Instead of Triggers and After Triggers?

SQL Server 2012 has two kinds of transaction triggers (DML Triggers): instead of triggers and after triggers. They differ in their purpose, timing, and effect, as detailed:

Instead of Triggers	After Triggers
DML statement simulated but not executed.	DML statement Executed, but can be rolled back in trigger.
It will execute before PK and FK constraints.	If will execute after the transaction is complete, but before it is committed.
You can create it on View or Table.	You can create it only on Table.
Only one per table and per event.	You can have multiple.

8. Who can create and execute the Triggers?

Only the table or view owner, members of the sysadmin fixed server role, or the dbowner or ddldmin fixed database roles have permission to create, alter, drop, enable, or disable triggers.

A trigger executes under the security context of the owner of their parent object unless modified with the EXECUTE AS clause when the trigger is created or altered.

9. What are Nested and Recursive Triggers?

Trigger events can be fired within another trigger action. One Trigger execution can trigger even on another table or same table. This trigger is called NESTED TRIGGER or RECURSIVE TRIGGER.

Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. INSTEAD OF triggers can be nested regardless of this setting.

Nested Triggers - Nesting means, when a trigger fired, it will also cause another trigger to be fired. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. Triggers (DML and DDL) can be nested to a maximum of 32 levels.

If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled with error message.

To disable nested triggers, set nested triggers option of sp_configure to 0 (off). The default configuration allows for nested triggers.

--stop executing all the nested triggers.

sp_CONFIGURE 'nested_triggers', 0

RECONFIGURE

Recursive Triggers - A recursive trigger is a unique type of nested AFTER trigger. If a trigger executes a DML statement that causes itself to fire, then it's a recursive triggerSQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE. Recursive triggers enable the following types of recursion to occur:

·         Indirect recursion: With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger TR2 then fires and updates table T1.

·         Direct recursion: With direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.

--Alternate way to stop Trigger Recursion

ALTER DATABASE databasename

SET RECURSIVE_TRIGGERS ON | OFF

Note: Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0. Note that nested triggers is a server option, whereas recursive triggers is a database option.

Note: Triggers execute within a transaction, a failure at any level of a set of nested triggers cancels the entire transaction, and all data modifications are rolled back. Include PRINT statements in your triggers so that you can determine where failure has occurred.

 10. What is Deferred Name Resolution?

In one of my training class, a developer asked me “when he creates any Stored Procedure  with table does not exist in database, the SP will creates without any error, while executing it gives run time error. However, if there is any valid table from database is referenced in SP with incorrect column name it will not let user create SP and gives error” why?.

SQL Server allows for Transact-SQL stored procedures, triggers, and batches to refer to tables that do not exist at compile time. This ability is called deferred name resolution.

Follow the link for more detail: http://msdn.microsoft.com/en-us/library/ms190686(v=sql.110).aspx

11. Difference between Triggers and Constraints?

Triggers support all of the functionality of constraints. But constraints are automatically enforced by the database engine and require less overhead than do triggers. Therefore, constraints should be used if possible, unless the application needs are more complex than can be enforced by constraints.

Enforcing entity integrity should always be done using constraints. Entity integrity refers to the uniqueness of rows. Primary keys and unique constraints are nearly always the best way of enforcing entity integrity.

When evaluating the use of constraints or triggers, always use constraints if they can meet the functional needs of the application. A trigger should be considered if custom error messages are required in response to error conditions, or in situations where more complex logic is required than constraints can provide.

12. Can we return Results in Triggers?

Yes, you can return results from trigger. There are three kinds of return values that are interesting:

·         Result sets

·         Raiserror messages

·         Rowcount messages

Also note that this is an overall system configuration. So it will disallow results from triggers in all of your databases.  It is never a good idea to return data from a trigger, but it is actually pretty useful for testing.

LOGON triggers always disallow results sets to be returned, and this behavior is not configurable. If a LOGON trigger does generate a result set, the trigger fails to execute and the login attempt that fired the trigger is denied.

We are enabling an advance feature ‘disallow results from triggers’ to 1 to enable this feature in the earlier version of SQL Server and it is also recommended by SQL Server to make it 1.

sp_configure 'disallow results from triggers', 1

GO

RECONFIGURE

When set to 1, the disallow results from triggers option is set to ON. The default setting for this option is 0 (OFF). If this option is set to 1 (ON), any attempt by a trigger to return a result set fails.

Note: The ability to return results from triggers will be removed in a future version of SQL Server. Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this.

13. What are uses of WITH APPEND?

WITH APPEND only applies when you are running in 6.5 compatibility mode (which can be set using sp_dbcmptlevel). SQL Server 6.5 and prior did not allow multiple triggers of the same type on any single table. . Once one update (or insert, or delete) trigger was created, that was it — you couldn’t create another trigger for the same type of action.

With SQL Server 7.0, the rules changed substantially. No longer do we have to worry about how many triggers we have for one type of action query.

When running our database in 6.5 compatibility modes, though, we run into a problem - our database is still working on the notion that there can only be one trigger of a given type on a given table. WITH APPEND gets around this problem by explicitly telling SQL Server that we want to add this new trigger even though we already have a trigger of that type on the table.  

14. What are trigger options (WITH ENCRYPTION and EXECUTE AS)?

The DDL/DML trigger creation options, ENCRYPTION and EXECUTE AS, both ensure the security of system-level auditing triggers.

WITH ENCRYPTION: Obfuscates the text of the CREATE TRIGGER statement.

This works just as it does for views and sprocs. If you add this option, no one will be able to view your code (not even you!). This is particularly useful if you are going to be building software for commercial distribution, or if you are concerned about security and don’t want your users to be able to see what data you’re modifying or accessing. Obviously, you should keep a copy of the code required to create the trigger somewhere else, in case you want to re-create it sometime later.

Using WITH ENCRYPTION prevents the trigger from being published as part of SQL Server replication.

WITH ENCRYPTION cannot be specified for CLR triggers.

The following DDL trigger will be encrypted when stored:

CREATE TRIGGER DDL_DDL_Level_Sample

ON ALL SERVER

WITH ENCRYPTION

FOR DDL_EVENTS

AS

Your code

EXECUTE AS: As with stored procedures, triggers can be executed under a different security context. Instead of the user who issued the DDL command that caused the DDL trigger to fire, the trigger can execute as one of the following security contexts:

·         Caller: Executes as the person executing the DDL command that fires the DDL trigger

·         Self: Executes as the person who created the DDL trigger

·         login_name: Executes as a specific login

Visit the link for more detail: http://msdn.microsoft.com/en-us/library/ms188354.aspx

15. Tell me few uses of DDL triggers.

1.    Saving a log of schema modifications for a database
2.    Saving a log of permission changes for a database
3.    Disallowing certain actions such as DROP statements for production databases
4.    Building an index modification log
5.     Auditing server-wide activity such as creating databases or linked servers.
6.     DDL triggers are useful for auditing server-level and database changes. They can easily find out which objects were changed, who changed them, and even undo unauthorized changes. 
7.    DDL triggers fire as the result of some server-level or database schema–level event. Where DML triggers respond to data changes, DDL triggers respond to schema changes. 
8.  Before developing DDL trigger, you must decide the scope of trigger - server or database level events will fire a trigger.