SQL Server 2005 Insert Trigger with Update Statement

Question

I am currently not in a location to test any of this out but would like to know if this is an option so I can start designing the solution in my head.

I would like to create an insert trigger on a table. In this insert trigger, I would like to get values from the inserted virtual table and use them to UPDATE the same table. Would this work or would we enter some kind of infinite loop (even though the trigger is not for update commands).

As an example if a row was inserted (which represents a new rate/cost for a vendor) I would like to update the same table to expire the old rate/cost for that vendor. The expiration is necessary vs updating the record that already exists so a history of rates/costs can be kept for reporting purposes (not to mention that the current reporting infrastructure expects this type of thing to happen and we are migrating current reports/data to SQL Server).

Thanks!

Answer 1

If you have only an INSERT trigger and no UPDATE trigger then there isn't any problem, but I assume you want to catch also UPDATEs and perhaps even DELETEs.

The INSTEAD OF triggers are guaranteed not to behave recursively:

If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively

With and INSTEAD OF trigger you must do both the original INSERT and the UPDATE you desire.

Answer 2

This doesn't sound like it would cause any problems to me, providing you're not doing an INSERT in another UPDATE trigger.