Sql Server Interview Question:Trigger

What is trigger?
Creates a trigger, which is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table. Microsoft® SQL Server™ allows the creation of multiple triggers for any given INSERT, UPDATE, or DELETE statement

Can we write trigger on view?
Table | view
Is the table or view on which the trigger is executed and is sometimes called the trigger table or trigger view. Specifying the owner name of the table or view is optional.

What is WITH ENCRYPTION in trigger?
Encrypts the syscomments entries that contain the text of CREATE TRIGGER. Using WITH ENCRYPTION prevents the trigger from being published as part of SQL Server replication.

What is AFTER trigger?
Specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger executes.
AFTER is the default, if FOR is the only keyword specified.

Can we write after trigger on views?
AFTER triggers cannot be defined on views.

What is INSTEAD OF trigger?
Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements.

At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, it is possible to define views on views where each view has its own INSTEAD OF trigger.

INSTEAD OF triggers are not allowed on updateable views WITH CHECK OPTION. SQL Server will raise an error if an INSTEAD OF trigger is added to an updateable view WITH CHECK OPTION specified. The user must remove that option using ALTER VIEW before defining the INSTEAD OF trigger

What are the data modification statement execute the trigger?

DELETE] [,] [INSERT] [,] [UPDATE] }
Are keywords that specify which data modification statements, when attempted against this table or view, activate the trigger. At least one option must be specified. Any combination of these in any order is allowed in the trigger definition. If more than one option is specified, separate the options with commas.

What are the logical table trigger can have?
deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:
SELECT *
FROM deleted

What are the datatypes not supported for trigger?

In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is equal to 70.
The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table.
When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.

How many columns we can add on update trigger?
IF UPDATE (column)
Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations. More than one column can be specified. Because the table name is specified in the ON clause, do not include the table name before the column name in an IF UPDATE clause.

To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. IF UPDATE will return the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

How do you enforce business rules by trigger?
Triggers are often used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the table creation statements (ALTER TABLE and CREATE TABLE);

however, DRI does not provide cross-database referential integrity. To enforce referential integrity (rules about the relationships between the primary and foreign keys of tables), use primary and foreign key constraints (the PRIMARY KEY and FOREIGN KEY
keywords of ALTER TABLE and CREATE TABLE).

If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed (fired).

The first and last AFTER triggers to be executed on a table may be specified by using sp_settriggerorder. Only one first and one last AFTER trigger for each of the INSERT, UPDATE, and DELETE operations may be specified on a table; if there are other AFTER triggers on the same table, they are executed randomly.
If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and the order value must be reset with

sp_settriggerorder.
An AFTER trigger is executed only after the triggering SQL statement, including all referential cascade actions and constraint checks associated with the object updated or deleted, has executed successfully.

The AFTER trigger sees the effects of the triggering statement as well as all referential cascade UPDATE and DELETE actions caused by the triggering statement.

What are Trigger Limitations?
CREATE TRIGGER must be the first statement in the batch and can apply to only one table.

A trigger is created only in the current database; however, a trigger can reference objects outside the current database.
If the trigger owner name is specified (to qualify the trigger), qualify the table name in the same way.

The same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.

INSTEAD OF DELETE/UPDATE triggers cannot be defined on a table that has a foreign key with a cascade on DELETE/UPDATE action defined.

Any SET statement can be specified inside a trigger. The SET option chosen remains in effect during the execution of the trigger and then reverts to its former setting.

When a trigger fires, results are returned to the calling application, just as with stored procedures. To eliminate having results returned to an application due to a trigger firing, do not include either SELECT statements that return results, or statements that perform variable assignment in a trigger.

A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the beginning of the trigger to eliminate the return of any result sets.

A TRUNCATE TABLE statement is not caught by a DELETE trigger. Although a TRUNCATE TABLE statement is, in effect, a DELETE without a WHERE clause (it removes all rows), it is not logged and thus cannot execute a trigger.

Because permission for the TRUNCATE TABLE statement defaults to the table owner and is not transferable, only the table owner should be concerned about inadvertently circumventing a DELETE trigger with a TRUNCATE TABLE statement.

The WRITETEXT statement, whether logged or unlogged, does not activate a trigger.
These Transact-SQL statements are not allowed in a trigger:

ALTER DATABASE CREATE DATABASE DISK INIT
DISK RESIZE DROP DATABASE LOAD DATABASE
LOAD LOG RECONFIGURE RESTORE DATABASE
RESTORE LOG

What is Recursive Triggers?
SQL Server also allows recursive invocation of triggers when the recursive triggers setting is enabled in sp_dboption.

Recursive triggers allow two types of recursion to occur:
Indirect recursion
Direct recursion

With indirect recursion, an application updates table T1, which fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.

With direct recursion, the application updates table T1, which fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.
This example uses both indirect and direct trigger recursion. Assume that two update triggers, TR1 and TR2, are defined on table T1.

Trigger TR1 updates table T1 recursively. An UPDATE statement executes each TR1 and TR2 one time. In addition, the execution of TR1 triggers the execution of TR1 (recursively) and TR2. The inserted and deleted tables for a given trigger contain rows corresponding only to the UPDATE statement that invoked the trigger.

How many levels we can have nested triggers?

Nested Triggers
Triggers can be nested to a maximum of 32 levels. 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.

If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows nested triggers.

If nested triggers is off, recursive triggers is also disabled, regardless of the recursive triggers setting of sp_dboption.

How do you ALTER TRIGGER?

Alters the definition of a trigger created previously by the CREATE TRIGGER statement
ALTER TRIGGER supports manually updateable views through INSTEAD OF triggers on tables and views. Microsoft® SQL Server™ applies ALTER TRIGGER the same way for all types of triggers (AFTER, INSTEAD-OF).

The first and last AFTER triggers to be executed on a table may be specified by using sp_settriggerorder. Only one first and one last AFTER trigger may be specified on a table; if there are other AFTER triggers on the same table, they will be executed in an undefined sequence.

If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and the order value must be reset with sp_settriggerorder.

An AFTER trigger is executed only after the triggering SQL statement, including all referential cascade actions and constraint checks associated with the object updated or deleted, is executed successfully.

The AFTER trigger operation checks for the effects of the triggering statement as well as all referential cascade UPDATE and DELETE actions caused by the triggering statement.

When a DELETE action to a child or referencing table is the result of a CASCADE on a DELETE from the parent table, and an INSTEAD OF trigger on DELETE is defined on that child table, the trigger is ignored and the DELETE action is executed.

How do you DROP TRIGGER?

Removes one or more triggers from the current database.
You can remove a trigger by dropping it or by dropping the trigger table.

When a table is dropped, all associated triggers are also dropped. When a trigger is dropped, information about the trigger is removed from the sysobjects and syscomments system tables.

Use DROP TRIGGER and CREATE TRIGGER to rename a trigger. Use ALTER TRIGGER to change the definition of a trigger.

For more information about determining dependencies for a specific trigger, see "sp_depends" in this volume.

For more information about viewing the text of the trigger, see "sp_helptext" in this volume.

How do you see existing trigger list?
For more information about viewing a list of existing triggers, see "sp_helptrigger" in this volume.