SQL server triggers interview questions and answers

SQL server triggers interview questions and answers for freshers and experienced candidates. These interview questions and answers on SQL server triggers will help you strengthen your technical skills, prepare for the interviews and quickly revise the concepts. Many candidates appear for the interview for one role - many of the them give the right answers to the questions asked. The one who provides the best answer with a perfect presentation is the one who wins the interview race. The set of SQL server triggers interview questions here ensures that you offer a perfect answer to the interview questions posed to you.
Ask a question
          

SQL Server triggers Interview questions

 

<<Previous  Next>>

Define Triggers.
What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
Describe triggers features and limitations.
Syntax for viewing, dropping and disabling triggers
Determine how to use the inserted and deleted pseudo tables.
Explain how to apply cascading referential integrity in place of triggers.
Explain trigger classes i.e. instead of and after trigger.
What are the instances when triggers are appropriate?

SQL Server triggers - August 29, 2008 at 18:00 PM by Nishant Kumar

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


SQL Server triggers- Posted on August 29, 2008 at 18:00 PM by Amit Satpute

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.


Triggers - October 24, 2008 at 18:10 pm by Rajmeet Ghai

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

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 ]

Determine how to use the inserted and deleted pseudo tables.

Inserted and deleted pseudo tables contain the New and Old values of the data that initiating the Trigger. These tables can be used for database maintenance and dynamic modification to data. These tables can be examined by the trigger itself. The tables themselves cannot be altered.

Explain how to apply cascading referential integrity in place of triggers.

Cascading referential integrity constraints are automatic updates and deletes on dependant objects. They define a set of actions that SQL server may need to take. The References clause of the CREATE TABLE and ALTER TABLE statements support ON DELETE and ON UPDATE clauses:

  • [ON DELETE {NO ACTION} ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and DELETE is rolled back.
  • [ON UPDATE {NO ACTION } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and UPDATE is rolled back.
  • [ ON DELETE { CASCADE } ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted
  • [ ON UPDATE { CASCADE } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also Updated.

NO ACTION is default.

Explain trigger classes i.e. instead of and after trigger.

Answer
INSTEAD OF
: Cause the trigger to fire instead of executing the triggering event or action. It prevents unnecessary changes to be made.

Example: Causes the trigger to fire instead of the update (action)

CREATE TRIGGER Employee_update ON Employee INSTEAD OF UPDATE AS { TRIGGER Definition }

AFTER: execute following the triggering action, such as an insert, update, or delete. These triggers are fired a little late in the process.

Example: Causes the trigger to fire instead of the update (action)

CREATE TRIGGER Employee_update ON Employee AFTER UPDATE AS { TRIGGER Definition }

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.


<<Previous  Next >>

Also read

What are the rules to use the ROWGUIDCOL property to define a globally unique identifier column?

Answer - A table can have only one ROWGUIDCOL column......

What are the commands available for Summarizing Data in SQL Server?

Answer - We have CUBE or ROLLUP operators to generate summary reports. Both are part of the GROUP BY....

What are the guidelines to use bulk copy utility of SQL Server?

Answer - While importing data, the destination table must already exist......

What are the ways to controlling Cursor Behavior?

Answer - Cursors behavior can be controlled by dividing them into cursor types: forward-only, static,........

What are the events recorded in a transaction log?

Answer - The start and end of each transaction......  

Define Truncate and Delete commands.

Answer - Truncate command is used to remove all rows of the column.The removed records are not recorded in the transaction log...... 

Define transaction and transaction isolation levels.

Answer - A transaction is a set of operations that works as a single unit. The ransactions can be categorized into explicit, autocommit, and implicit....

Describe in brief authentication modes in SQL server.

Answer - This is the default and recommended security mode. In this mode, access to SQL server is controlled by Windows NT.....

Define @@Error and raiseerror.

Answer - It is system variable that returns error code of the SQL statement.....



Write your comment - Share Knowledge and Experience


 
Interview questions
 
Home | Login | About us | Sitemap | Contact us