Implementing Triggers (Single Marks questions)

[1]. You have applied constraints, an INSTEAD OF trigger, and three AFTER triggers to a table. A colleague tells you that there is no way to control trigger order for the table. Is he correct? Why or why not?

He is incorrect. INSTEAD OF triggers always fire before constraints are processed. Following constraint processing, the AFTER triggers fire. Because there are three AFTER triggers, you can be sure about their execution order by using sp_settriggerorder to define the first and last trigger to execute.

[2]. You need to make sure that when a primary key is updated in one table, all foreign key references to it are also updated. How should you accomplish this task?

Configure cascading referential integrity to the foreign key constraints so that updates to the primary key are propagated to the other tables.

[3]. Name four instances when triggers are appropriate.

Triggers are appropriate in the following instances:

• If using declarative data integrity methods does not meet the functional needs of the application
• If changes must cascade through related tables in the database
• If the database is denormalized and requires an automated way to update redundant data contained in multiple tables
• If a value in one table must be validated against a non-identical value in another table
• If customized messages and complex error handling are required

[4]. When a trigger fires, how does it track the changes that have been made to the modified table?

An INSERT or UPDATE trigger creates the Inserted (pseudo) table in memory. The Inserted table contains any inserted or updated data. The UPDATE trigger also creates the Deleted (pseudo) table, which contains the original data. A DELETE trigger also creates a Deleted (pseudo) table in memory. The Deleted table contains any deleted data. The transaction isn't committed until the trigger completes. Thus, the trigger can roll back the transaction.

[5]. Name a table deletion event that does not fire a DELETE trigger.

TRUNCATE TABLE does not fire a DELETE trigger because the transaction isn't logged. Logging the transaction is critical for trigger functions, because without it, there is no way for the trigger to track changes and roll back the transaction if necessary.

[6]. Name a system stored procedure and a function used to view the properties of a trigger.

The sp_helptrigger system stored procedure shows the properties of one or all triggers applied to a table or view. The OBJECTPROPERTY function is used to determine the properties of database objects (such as triggers). For example, the following code returns 1 if a trigger named Trigger01 is an INSTEAD OF trigger:

SELECT OBJECTPROPERTY (OBJECT_ID(`trigger01'), `ExecIsInsteadOfTrigger')

[7]. Using Transact-SQL language, what are two methods to stop a trigger from running?

You can use the ALTER TABLE statement to disable a trigger. For example, to disable a trigger named Trigger01 that is applied to a table named Table01, type the following:

ALTER TABLE table01 DISABLE TRIGGER trigger01.

A second option is to delete the trigger from the table by using the DROP TRIGGER statement.

[8]. Write a (COLUMNS_UPDATED()) clause that detects whether columns 10 and 11 are updated.

IF ((SUBSTRING(COLUMNS_UPDATED(),2,1)=6))

 PRINT 'Both columns 10 and 11 were updated.'

[9]. Name three common database tasks accomplished with triggers.

Maintaining running totals and other computed values; creating audit records; invoking external actions; and implementing complex data integrity

[10]. What command can you use to prevent a trigger from displaying row count information to a calling application?

In the trigger, type the following:

SET NOCOUNT ON

There is no need to include SET NOCOUNT OFF before exiting the trigger, because system settings configured in a trigger are only in effect while the trigger is running.

[11]. What type of event creates both an Inserted and Deleted logical table?

An UPDATE event is the only type of event that creates both pseudo tables. The Inserted table contains the new value specified in the update, and the Deleted table contains the original value before the UPDATE runs.

[12]. Is it possible to instruct a trigger to display result sets and print messages?

Yes, it is possible to display result sets by using the SELECT statement and print messages to the screen by using the PRINT command. You shouldn't use SELECT and PRINT to return a result, however, unless you know that all applications that will modify tables in the database can handle the returned data.