Other Topics > Smart Access 1996-2006 > Feb-2003 > Using Triggers

Using Triggers

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Feb-2003 >

Using Triggers

Russell Sinclair

 

Last month, Russell Sinclair explained the anatomy of triggers

and how they work. In this month’s installment of “Working

T-SQL,” he shows you how to write triggers and how to put

them to work in your database application.

 

LAST month, I explained what triggers are, how they

work, and what functionality they provide. I didn’t

provide any code examples for how to use them. As

I explained then, this is because the next version of SQL

Server (currently codenamed “Yukon”) is going to be

programmable in any .NET-compliant language. You

won’t be limited to Transact-SQL for any code you need

to write for your database.

 

That being said, Yukon is still a long way away from

release. In fact, it hasn’t even been released to public beta

yet. Since it’s unlikely that you want to wait for a year or

two until Yukon is released, this month I’ll take you

through the syntax of writing triggers, and explain some

of the more common problems you’ll run into when you

write them. I’ll also show you how and when to use them

to replace VBA code.

 

Trigger syntax

 

You can use this dialog to create, edit, and delete

triggers as necessary. When you choose to create a new

trigger or edit an existing one, you’ll be taken to a

SQL editor window that will allow you to modify the

trigger’s definition.

 

One of the most common examples used with triggers

is inventory management. When a user places an order

(that is, a new record is added to the Orders table), you

want to automatically update inventory values (in other

words, update the Inventory table by removing the

ordered items from stock). This situation is the ideal

situation in which to use an AFTER trigger. The Northwind

database uses a Products table to keep track of inventory

levels. This isn’t the best example, but I’ll use it because

it’s the one database that I know that you have a copy of.

Two fields in the Products table need to be managed—

UnitsInStock and UnitsOnOrder. When a user adds a new

Order Detail, we want these values to be updated. A

trigger that handles this situation would look like this:

 

CREATE TRIGGER trgOrderInventoryInsert

ON dbo.[Order Details]

AFTER INSERT

AS

The basic syntax for creating a trigger is:

CREATE TRIGGER trigger_name ON {table|view}

{FOR|AFTER|INSTEAD OF}

{[INSERT] [,] [UPDATE] [,] [DELETE]}

AS sql_statement

 

 

 

200302_rs1

Figure 1

 

Read more in the pdf article Using Triggers