Trigger Question

[dBjason]

Hello,

I'm trying to port an old MS Access db into SQL Server Express or MSDE 2000. A 3rd party .NET interface provides for data entry. Unfortunately we are unable to edit/manipulate the interface.

A part of the original application allowed the user to prioritize tasks. There was a numeric field, used for sorting. Values were generally 1-100, with a few 'empty' numbers inbetween, to allow for new tasks to be prioritized inbetween existing tasks. If two records had values of '55' and '57', and a new record was inserted (or updated) with '56', the old back-end code would update the prioity fields to '50', '55' and '60', to allow for future prioritzed entries -- without having to manually edit other tasks' priorities in the recordset.

The new interface has a numeric field for data entry, but no back-end code to handle the updates.

I agree it's not the best way to do it, but it's what they've given me.


So my question is this: If I have a trigger set to fire on update, can it update the column it was setup to fire on without calling itself recursively?

I'd be running an UPDATE statement in a trigger, updating the same column the trigger was created to fire when updated. Any way around this?

Thanks in advance for any/all help!

-Jason

 

[gmmastros]

According to this:

http://msdn.microsoft.com/en-us/library/aa214644(SQL.80).aspx

A trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set.

To see if recursive triggers is enabled in your database:

sp_dboption YourDatabaseNameHere, 'recursive triggers'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dBjason]

Thanks!