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.

Using Transact-SQL on a SQL Server Database (Single Marks Question)

Using Transact-SQL on a SQL Server Database

[1]. In which window in Query Analyzer can you enter and execute Transact-SQL statements?

The Editor pane of the Query window

[2]. How do you execute Transact-SQL statements and scripts in Query Analyzer?

You can execute a complete script or an individual Transact-SQL statement by creating or opening the script in the Editor pane and then pressing F5. To perform this task, no other statements can be entered into the Editor pane. If there are other statements, you must highlight the script or statements that you want to execute, then press F5.

[3]. What type of information is displayed on the Execution Plan tab, the Trace tab, and the Statistics tab?

The Execution Plan tab displays a graphical representation of the execution plan that is used to execute the current query. The Trace tab, like the Execution Plan tab, can assist you with analyzing your queries. The Trace tab displays server trace information about the event class, subclass, integer data, text data, database ID, duration, start time, reads and writes, and CPU usage. The Statistics tab provides detailed information about client-side statistics for execution of the query.

[4]. Which tool in Query Analyzer enables you to control and monitor the execution of stored procedures?

Transact-SQL debugger

[5]. What is Transact-SQL?

Transact-SQL is a language that contains the commands used to administer instances of SQL Server; to create and manage all objects in an instance of SQL Server; and to insert, retrieve, modify, and delete data in SQL Server tables. Transact-SQL is an extension of the language defined in the SQL standards published by ISO and ANSI.

[6]. What are the three types of Transact-SQL statements that SQL Server supports?

DDL, DCL, and DML

[7]. What type of Transact-SQL statement is the CREATE TABLE statement?

DDL

[8]. What Transact-SQL element is an object in batches and scripts that can hold a data value?

Variable

[9]. Which Transact-SQL statements do you use to create, modify, and delete a user-defined function?

CREATE FUNCTION, ALTER FUNCTION, and DROP FUNCTION

[10]. What are control-of-flow language elements?

Control-of-flow language elements control the flow of execution of Transact-SQL statements, statement blocks, and stored procedures. These words can be used in Transact-SQL statements, batches, and stored procedures. Without control-of-flow language, separate Transact-SQL statements are performed sequentially, as they occur. Control-of-flow language elements permit statements to be connected, related to each other, and made interdependent by using programming-like constructs.

Control-of-flow keywords are useful when you need to direct Transact-SQL to take some kind of action. For example, use a BEGIN...END pair of statements when including more than one Transact-SQL statement in a logical block. Use an IF...ELSE pair of statements when a certain statement or block of statements needs to be executed IF some condition is met, and another statement or block of statements should be executed if that condition is not met (the ELSE condition).

[11]. What are some of the methods that SQL Server 2000 supports for executing Transact-SQL statements?

You can execute single statements, or you can execute the statements as a batch (a group of one or more Transact-SQL statements). You can also execute Transact-SQL statements through stored procedures and triggers. In addition, you can use scripts to execute Transact-SQL statements.

[12]. What are the differences among batches, stored procedures, and triggers?

A batch is a group of one or more Transact-SQL statements sent at one time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time. A stored procedure is a group of Transact-SQL statements that is compiled one time and can then be executed many times. A trigger is a special type of stored procedure that a user does not call directly. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.