I have a table which, until now had an update trigger only.
That trigger was used to record the record as it existed BEFORE the change,
and who made the change. It works fine.
Now I have a situation where a program will INSERT a row into the table
(which now has a new field). The value of the field is calculated. I have
two choices,
1) Re-write a lot of code to calculate the value before the insert
2) Write a simple insert trigger to calculate the value and update it in
the table.
Problem:
If I use the insert trigger to update the value, I am doing an update. The
update trigger will fire. I do not want the update trigger to fire when this
operation is performed.
Is there a way I can add code to the Insert trigger to tell it that when it
updates the value it should prevent the update trigger from firing'
Thanks.Roger,
If the value is calculated:
Can you use a calculated column?
Can you calculate the value in the select statement?
AMB
"Roger Twomey" wrote:
> I have a table which, until now had an update trigger only.
> That trigger was used to record the record as it existed BEFORE the change
,
> and who made the change. It works fine.
> Now I have a situation where a program will INSERT a row into the table
> (which now has a new field). The value of the field is calculated. I have
> two choices,
> 1) Re-write a lot of code to calculate the value before the insert
> 2) Write a simple insert trigger to calculate the value and update it i
n
> the table.
> Problem:
> If I use the insert trigger to update the value, I am doing an update. The
> update trigger will fire. I do not want the update trigger to fire when th
is
> operation is performed.
> Is there a way I can add code to the Insert trigger to tell it that when i
t
> updates the value it should prevent the update trigger from firing'
> Thanks.
>
>|||We cannot use a calculated column as the values must not change and the
values used to calculate can change over time.
We are currently using select statements that calculate but the process is
labourious and complex so we are trying to force consistent data with lower
cpu utilization (One insert is read many many times)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B0B63AC0-5557-4C66-83DE-BBFC526E8C44@.microsoft.com...
> Roger,
> If the value is calculated:
> Can you use a calculated column?
> Can you calculate the value in the select statement?
>
> AMB
> "Roger Twomey" wrote:
>|||Roger,
If I understood, you calculate the value during the insert but you want it
to be static after it is done?, In this case you can stop the update trigger
.
...
if update(calculated_column) return
...
If you do not use bulk insert, then I would recommend to insert the row
using a stored procedure. If as you said, the calculation is labourious and
complex, doing it in a trigger will make the transaction longer and will
impact the performance of the engine and scalability of the db.
AMB
"Roger Twomey" wrote:
> We cannot use a calculated column as the values must not change and the
> values used to calculate can change over time.
> We are currently using select statements that calculate but the process is
> labourious and complex so we are trying to force consistent data with lowe
r
> cpu utilization (One insert is read many many times)
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:B0B63AC0-5557-4C66-83DE-BBFC526E8C44@.microsoft.com...
>
>|||That is true, but the impact will only occur on insert, not on every read.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3CF33806-F33D-433E-8305-0450AF8E938B@.microsoft.com...
> Roger,
> If I understood, you calculate the value during the insert but you want it
> to be static after it is done?, In this case you can stop the update
> trigger.
> ...
> if update(calculated_column) return
> ...
> If you do not use bulk insert, then I would recommend to insert the row
> using a stored procedure. If as you said, the calculation is labourious
> and
> complex, doing it in a trigger will make the transaction longer and will
> impact the performance of the engine and scalability of the db.
>
> AMB
> "Roger Twomey" wrote:
>|||Thanks for the tip (If update(calculated_column) return)
I think that should work.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3CF33806-F33D-433E-8305-0450AF8E938B@.microsoft.com...
> Roger,
> If I understood, you calculate the value during the insert but you want it
> to be static after it is done?, In this case you can stop the update
> trigger.
> ...
> if update(calculated_column) return
> ...
> If you do not use bulk insert, then I would recommend to insert the row
> using a stored procedure. If as you said, the calculation is labourious
> and
> complex, doing it in a trigger will make the transaction longer and will
> impact the performance of the engine and scalability of the db.
>
> AMB
> "Roger Twomey" wrote:
>|||On Mon, 28 Feb 2005 13:07:46 -0500, Roger Twomey wrote:
>Thanks for the tip (If update(calculated_column) return)
>I think that should work.
Hi Roger,
That will only work if the calculated_column is never updated from other
sources than the insert trigger.
Other options to investigate are:
* Check if it's possible to disable nested triggers (note: this can only
be done at the server level; it will affect all databases on the
server).
* Checking the value of TRIGGER_NESTLEVEL() (or maybe even the value of
TRIGGER_NESTLEVEL(object_ID('upd_trigger
'))).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
