+91 90691 39140 | +1 253 214 3115 | info@hub4tech.com | hub4tech

SQL Server Triggers Interview Questions and Answers

1
Define 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

2
How many triggers you can have on a table?

A table can have up to 12 triggers defined on it.

3
What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers are constructs in PL/SQL that need to be just created and associated with a table. Once they are created, when the table associated with it gets updated due to an UPDATE, INSERT or a DELETE, the triggers get implicitly fired depending upon the instructions passed to them.

A table can have up to 12 triggers defined on it.

Triggers can't be invoked on demand. They get triggered when the associated INSERT, DELETE or UPDATE is performed.

4
Explain 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
Syntax: DROP TRIGGER Trigger_name

Disable a trigger:-
Syntax: DISABLE TRIGGER [schema name] trigger name
ON [object, database or ALL server ]

5
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
Copyright ©2015 Hub4Tech.com, All Rights Reserved. Hub4Tech™ is registered trademark of Hub4tech Portal Services Pvt. Ltd.
All trademarks and logos appearing on this website are the property of their respective owners.
FOLLOW US