What are triggers? What are the different types

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

What are triggers? What are the different types of triggers in SQL Server 2000?

It's very beneficial for a potential database developer to know the types of triggers available, and how to implement them.

A trigger is a specialized type of stored procedure that is bound to a table or view in SQL Server 2000. In SQL Server 2000, there are INSTEAD-OF triggers and AFTER triggers. INSTEAD-OF triggers are procedures that execute in place of a Data Manipulation Language (DML) statement on a table. For example, if I have an INSTEAD-OF-UPDATE trigger on TableA, and I execute an update statement on that table, the code in the INSTEAD-OF-UPDATE trigger will execute instead of the update statement that I executed.

An AFTER trigger executes after a DML statement has taken place in the database. These types of triggers are very handy for auditing data changes that have occurred in your database tables.

(Continued on next question...)

Other Job Interview Questions

• What does the DUMP command do?
• What's the difference between a server login and a database user?
• In SQL Server 2000, how can you send an alert without sending a message to the client?
• In SQL Server 2005, what permissions does a user need to successfully execute the USE command?
• You want to disable the receipt of messages in one of your databases by the Service Broken. How should you do this?
• What does a return value of -1 mean from the function XACT_STATE() in SQL Server 2005?
• After you've completed a backup for your Sales server to disk...
• In the SQL Server 2005 thesaurus XML configuration file, what is the expansion set?
• Which of the following is true about the Raw File Source in SQL Server 2005 Integration Services?
• You need to remove a large table from your database ...
• More...