SQL Server interview questions | triggers all about triggers

Explain Triggers?

A trigger is a special type of event driven stored procedure. 

It gets initiated when Insert, Delete or Update event occurs. 

It can be used to maintain referential integrity. 

A trigger can call stored procedure.

Executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

You can specify which trigger fires first or fires last using sp_settriggerorder.

Triggers can't be invoked on demand.

They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens.

Triggers are generally used to implement business rules, auditing.

Triggers can also be used to extend the referential integrity checks

Trigger is one of the database objects and executes set of Transact SQL Statements Automatically in response to an event (INSERT, UPDATE,DELETE etc) with in database.
Generally Triggers are used to implement business rules.

How many triggers you can have on a table? A table can have up to 12 triggers defined on it.

How many types of triggers are there in Sql Server 2005?

There are two types of triggers
• Data Manipulation language (DML) triggers
• Data Definition language (DDL) triggers

DML triggers (implementation) will run when INSERT, UPDATE, or DELETE statements modify data in a specified table or view.

DDL triggers will run in response to DDL events that occur on the server such as creating, altering, or dropping an object, are used for database administration tasks

What are the different modes of firing triggers?

After Trigger: An AFTER trigger fires after SQL Server completes all actions successfully

Instead of Triggers: An INSTEAD OF trigger causes SQL Server to execute the code in the trigger instead of the operation that caused the trigger to fire.

Describe triggers features and limitations.

Trigger features:- 1. Can execute a batch of SQL code for an insert, update or delete command is executed 2. Business rules can be enforced on modification of data Trigger Limitations:- 1. Does not accept arguments or parameters 2. Cannot perform commit or rollback 3. Can cause table errors if poorly written What are the instances when triggers are appropriate? Answer When security is the top most priority. i.e. to allow unauthorized access When backups are essential When Maintenance is desired. Triggers can be fired when any error message is logged Keeping the database consistent. What is 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 is called a nested trigger. Syntax for viewing, dropping and disabling  triggers View trigger: A trigger can be viewed by using sp_helptrigger syntax. This returns all the triggers defined in a table. Sp_helptrigger table_name Drop a trigger DROP TRIGGER Trigger_name Disable a trigger:- DISABLE TRIGGER [schema name] trigger name ON [object, database or ALL server ]