Monday, March 19, 2012

Can i bypass a trigger

I have 2 tabels "realtime" and "historic"
Im getting updates on "realtime" and a trigger transferes the data to
"historic" if the some dates change.
I've made an sp that can update "realtime" and "historic", and it is more
efficiant than the trigger. I have several applikations that updates
realtime, wich I do not control, so I can not remove my trigger.
I do not want the updates to happen 2 times.
Is it possible in my sp to skip the trigger and do the historic update
itself ?
Best regards
MikaelMikael
ALTER TABLE ? DISABLE TRIGGER ALL --or trigger's name
"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:A6291B4D-1F13-4740-BF9B-A91C554252A6@.microsoft.com...
>I have 2 tabels "realtime" and "historic"
> Im getting updates on "realtime" and a trigger transferes the data to
> "historic" if the some dates change.
> I've made an sp that can update "realtime" and "historic", and it is more
> efficiant than the trigger. I have several applikations that updates
> realtime, wich I do not control, so I can not remove my trigger.
> I do not want the updates to happen 2 times.
> Is it possible in my sp to skip the trigger and do the historic update
> itself ?
>
> --
> Best regards
> Mikael|||"Mikael" <Mikael@.discussions.microsoft.com> wrote in message
news:A6291B4D-1F13-4740-BF9B-A91C554252A6@.microsoft.com...
>I have 2 tabels "realtime" and "historic"
> Im getting updates on "realtime" and a trigger transferes the data to
> "historic" if the some dates change.
> I've made an sp that can update "realtime" and "historic", and it is more
> efficiant than the trigger. I have several applikations that updates
> realtime, wich I do not control, so I can not remove my trigger.
>
Why not move your code from the SP into the trigger?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You could drop the trigger, disable the trigger, or simply incorporate the
more efficient logic from your procedure into the trigger.
That trigger is there for a reason, so disabling or dropping it may not be
the best choice.
Anyway, here are the options:
- drop a trigger:
drop trigger <trigger name>
go
- disable a trigger:
alter table <table name>
disable trigger <trigger name>
go
...and don't forget to re-enable it after you're done:
alter table <table name>
enable trigger <trigger name>
go
ML
http://milambda.blogspot.com/|||I want the trigger on the table for the other applikations.
I dont think it is recomenteble to disable the trigger in the sp and then
again enable it ?
Best regards
Mikael
"Uri Dimant" wrote:

> Mikael
> ALTER TABLE ? DISABLE TRIGGER ALL --or trigger's name
>
> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:A6291B4D-1F13-4740-BF9B-A91C554252A6@.microsoft.com...
>
>|||My execution plan shows me that 99% of the update is spend on getting the
affected rows from the inserted and deleted “tables”.
My table is updates frequently, and even though I suspect the plan to be
mistaken about 99%, I still would like to optimize the performance.
Best regards
Mikael
"David Portas" wrote:

> "Mikael" <Mikael@.discussions.microsoft.com> wrote in message
> news:A6291B4D-1F13-4740-BF9B-A91C554252A6@.microsoft.com...
> Why not move your code from the SP into the trigger?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>

No comments:

Post a Comment