Can i bypass a trigger

23 Mar 2006 9:48 AM

Mikael

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

23 Mar 2006 9:56 AM

Uri Dimant

Mikael
ALTER TABLE ? DISABLE TRIGGER ALL --or trigger's name

Show quote

"Mikael" <Mik***@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

23 Mar 2006 10:07 AM

Mikael

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

Show quote

"Uri Dimant" wrote:

> Mikael
> ALTER TABLE ? DISABLE TRIGGER ALL --or trigger's name
>
>
>
> "Mikael" <Mik***@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
>
>
>

23 Mar 2006 9:59 AM

David Portas

"Mikael" <Mik***@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
--

23 Mar 2006 10:20 AM

Mikael

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

Show quote

"David Portas" wrote:

> "Mikael" <Mik***@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
> --
>
>
>

23 Mar 2006 10:03 AM

ML

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/