Updating an Oracle Trigger for SQL Server 2000

I recently finished a computer programming degree for which I used Oracle, but now I'm using SQL Server 2000. In implementing a small Oracle sales and stock-control system, I was using BEFORE and AFTER triggers to update tables such as the order_line table. For example, if an order_line was updated, a BEFORE trigger would fire and add the old quantity value for the order_line to the Product table's Stock column. The AFTER trigger would then delete the new order_line quantity from the Product table's Stock column.Thus, no matter what changes were made to the stock quantity in the order_line, the Products table was correct.

The problem I have is that I can't find BEFORE triggers in SQL Server 2000—only INSTEAD OF triggers. Also, can I use the "old" and "new" value functions in SQL Server 2000 triggers?

As you've noticed, SQL Server's trigger architecture is somewhat different from Oracle's, but you should be able to get the effect you want. I see that you're basically "journaling;" you use the old order value to "zero out" the stock decrement, then apply the new value to reset the stock decrement correctly.

SQL Server gives you two tables, INSERTED and DELETED, that are available only to trigger code. Note that an update places a row in both tables.To do what you want, define a trigger like the one that Listing 1 shows.This code technique should handle order deletes, sku changes within an order line, and quantity updates. Note that Listing 1's code doesn't protect against negative stock balances or handle inserted orders. Hope this helps get you going!

—Patrick Conlan
Platform Program Manager
Microsoft Project Team