[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

What Owning Object called the trigger?

Rick Toner

3/22/2007 5:21:00 PM

Is there any way to find out what object fired a trigger? I need to know if
it was another trigger or a stored procedure.

Thanks
--
Rick Toner
7 Answers

Aaron [SQL Server MVP]

3/22/2007 5:53:00 PM

0

Well, a stored procedure doesn't really fire a trigger.

Anyway, you can tell if this is a nested trigger, at least, by checking
@@NESTLEVEL

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...





"Rick Toner" <RickTNoSpam@_PleaseNoSpam_@Access-ct.com> wrote in message
news:64307D00-314D-48E1-A132-6BEC94A4E3CC@microsoft.com...
> Is there any way to find out what object fired a trigger? I need to know
> if
> it was another trigger or a stored procedure.
>
> Thanks
> --
> Rick Toner


Tibor Karaszi

3/22/2007 6:37:00 PM

0

> Anyway, you can tell if this is a nested trigger, at least, by checking @@NESTLEVEL

Or perhaps get even more information about the status using also TRIGGER_NESTLEVEL(). Perhaps the
later only increases for triggers, while the former also increases for procedures. Never used it,
though, so I'm only thinking out loud.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OdaIarKbHHA.4520@TK2MSFTNGP06.phx.gbl...
> Well, a stored procedure doesn't really fire a trigger.
>
> Anyway, you can tell if this is a nested trigger, at least, by checking @@NESTLEVEL
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sq...
> http://www.aspfa...
>
>
>
>
>
> "Rick Toner" <RickTNoSpam@_PleaseNoSpam_@Access-ct.com> wrote in message
> news:64307D00-314D-48E1-A132-6BEC94A4E3CC@microsoft.com...
>> Is there any way to find out what object fired a trigger? I need to know if
>> it was another trigger or a stored procedure.
>>
>> Thanks
>> --
>> Rick Toner
>
>

Rick Toner

3/22/2007 7:18:00 PM

0

Thansk Tibor and Aaron,
The only thing I see about both of these is it will tell me an int return.
I am hoping for a string value so I can say:

DECLARE @ObjectName AS VARCHAR(255)
SET @ObjectName = 'LookupFunction' -- Hoping to have a Trigger/SP Object Name

IF @BojectName = 'Bla'
BEGIN
--Do not fire because this was expected
END
ELSE
BEGIN
--Fire Trigger
END

--
Rick Toner


"Tibor Karaszi" wrote:

> > Anyway, you can tell if this is a nested trigger, at least, by checking @@NESTLEVEL
>
> Or perhaps get even more information about the status using also TRIGGER_NESTLEVEL(). Perhaps the
> later only increases for triggers, while the former also increases for procedures. Never used it,
> though, so I'm only thinking out loud.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://sqlblog.com/blogs/tib...
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> news:OdaIarKbHHA.4520@TK2MSFTNGP06.phx.gbl...
> > Well, a stored procedure doesn't really fire a trigger.
> >
> > Anyway, you can tell if this is a nested trigger, at least, by checking @@NESTLEVEL
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.sq...
> > http://www.aspfa...
> >
> >
> >
> >
> >
> > "Rick Toner" <RickTNoSpam@_PleaseNoSpam_@Access-ct.com> wrote in message
> > news:64307D00-314D-48E1-A132-6BEC94A4E3CC@microsoft.com...
> >> Is there any way to find out what object fired a trigger? I need to know if
> >> it was another trigger or a stored procedure.
> >>
> >> Thanks
> >> --
> >> Rick Toner
> >
> >
>
>

bob

3/23/2007 11:58:00 AM

0

SQL Call Stack, if only!

You could move your logic out to the objects in question, ie

-- Pseudo code
CREATE PROC sp1

sp1 is not allowed to fire trigger, therefore

Disable triggers

-- Do main work

Re-enable triggers
GO

CREATE PROC sp2

sp1 IS allowed to fire trigger, therefore

-- Do main work
GO


"Rick Toner" wrote:

> Thansk Tibor and Aaron,
> The only thing I see about both of these is it will tell me an int return.
> I am hoping for a string value so I can say:
>
> DECLARE @ObjectName AS VARCHAR(255)
> SET @ObjectName = 'LookupFunction' -- Hoping to have a Trigger/SP Object Name
>
> IF @BojectName = 'Bla'
> BEGIN
> --Do not fire because this was expected
> END
> ELSE
> BEGIN
> --Fire Trigger
> END
>
> --
> Rick Toner
>
>
> "Tibor Karaszi" wrote:
>
> > > Anyway, you can tell if this is a nested trigger, at least, by checking @@NESTLEVEL
> >
> > Or perhaps get even more information about the status using also TRIGGER_NESTLEVEL(). Perhaps the
> > later only increases for triggers, while the former also increases for procedures. Never used it,
> > though, so I'm only thinking out loud.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/d...
> > http://sqlblog.com/blogs/tib...
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> > news:OdaIarKbHHA.4520@TK2MSFTNGP06.phx.gbl...
> > > Well, a stored procedure doesn't really fire a trigger.
> > >
> > > Anyway, you can tell if this is a nested trigger, at least, by checking @@NESTLEVEL
> > >
> > > --
> > > Aaron Bertrand
> > > SQL Server MVP
> > > http://www.sq...
> > > http://www.aspfa...
> > >
> > >
> > >
> > >
> > >
> > > "Rick Toner" <RickTNoSpam@_PleaseNoSpam_@Access-ct.com> wrote in message
> > > news:64307D00-314D-48E1-A132-6BEC94A4E3CC@microsoft.com...
> > >> Is there any way to find out what object fired a trigger? I need to know if
> > >> it was another trigger or a stored procedure.
> > >>
> > >> Thanks
> > >> --
> > >> Rick Toner
> > >
> > >
> >
> >

Rick Toner

3/23/2007 12:20:00 PM

0

Hi Bob,
Thanks for replying.
I see what you are doing. If i disable the Trigger in script, will it
disable "ALL" triggers for other actions outside of the one that is firing?

Also my biggest issue is I am in a insert trigger and it is firing an update
trigger when I don't want it to. If I disable triggers while in a trigger
will there be any issues?

Thanks
Rick

--
Rick Toner


"Bob" wrote:

> SQL Call Stack, if only!
>
> You could move your logic out to the objects in question, ie
>
> -- Pseudo code
> CREATE PROC sp1
>
> sp1 is not allowed to fire trigger, therefore
>
> Disable triggers
>
> -- Do main work
>
> Re-enable triggers
> GO
>
> CREATE PROC sp2
>
> sp1 IS allowed to fire trigger, therefore
>
> -- Do main work
> GO
>
>
> "Rick Toner" wrote:
>
> > Thansk Tibor and Aaron,
> > The only thing I see about both of these is it will tell me an int return.
> > I am hoping for a string value so I can say:
> >
> > DECLARE @ObjectName AS VARCHAR(255)
> > SET @ObjectName = 'LookupFunction' -- Hoping to have a Trigger/SP Object Name
> >
> > IF @BojectName = 'Bla'
> > BEGIN
> > --Do not fire because this was expected
> > END
> > ELSE
> > BEGIN
> > --Fire Trigger
> > END
> >
> > --
> > Rick Toner
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > > Anyway, you can tell if this is a nested trigger, at least, by checking @@NESTLEVEL
> > >
> > > Or perhaps get even more information about the status using also TRIGGER_NESTLEVEL(). Perhaps the
> > > later only increases for triggers, while the former also increases for procedures. Never used it,
> > > though, so I'm only thinking out loud.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/d...
> > > http://sqlblog.com/blogs/tib...
> > >
> > >
> > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
> > > news:OdaIarKbHHA.4520@TK2MSFTNGP06.phx.gbl...
> > > > Well, a stored procedure doesn't really fire a trigger.
> > > >
> > > > Anyway, you can tell if this is a nested trigger, at least, by checking @@NESTLEVEL
> > > >
> > > > --
> > > > Aaron Bertrand
> > > > SQL Server MVP
> > > > http://www.sq...
> > > > http://www.aspfa...
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Rick Toner" <RickTNoSpam@_PleaseNoSpam_@Access-ct.com> wrote in message
> > > > news:64307D00-314D-48E1-A132-6BEC94A4E3CC@microsoft.com...
> > > >> Is there any way to find out what object fired a trigger? I need to know if
> > > >> it was another trigger or a stored procedure.
> > > >>
> > > >> Thanks
> > > >> --
> > > >> Rick Toner
> > > >
> > > >
> > >
> > >

Hugo Kornelis

3/23/2007 11:50:00 PM

0

On Fri, 23 Mar 2007 05:20:03 -0700, Rick Toner
<RickTNoSpam@_PleaseNoSpam_@Access-ct.com> wrote:

>Hi Bob,
>Thanks for replying.
>I see what you are doing. If i disable the Trigger in script, will it
>disable "ALL" triggers for other actions outside of the one that is firing?

Hi Rick,

Yes. Disabling a trigger means that it also won't fire if another user
happens to update the table at that time.

>Also my biggest issue is I am in a insert trigger and it is firing an update
>trigger when I don't want it to. If I disable triggers while in a trigger
>will there be any issues?

You can use TRIGGER_NESTLEVEL(OBJECT_ID('triggername')) to check if the
trigger is called, directly or indirectly, from the specified trigger.
Or you can use TRIGGER_NESTLEVEL() to check if the trigger is called
from ANY trigger.

Another possible workaround is to add a dummy column to a table and
include that dummy column in updates that stem from a trigger. In the
UPDATE trigger, you can then use IF UPDATE(DummyColumn) to check if the
update comes from another trigger or not. Note that this is only
reliable if you can control all update statements fired at the database,
so if anyone has the possibility to write his or her own queries, this
is not safe.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Rick Toner

3/28/2007 12:57:00 AM

0

Thanks Hugo,
That looks like what I needed. I will give it a shot.

Rick
--
Rick Toner


"Hugo Kornelis" wrote:

> On Fri, 23 Mar 2007 05:20:03 -0700, Rick Toner
> <RickTNoSpam@_PleaseNoSpam_@Access-ct.com> wrote:
>
> >Hi Bob,
> >Thanks for replying.
> >I see what you are doing. If i disable the Trigger in script, will it
> >disable "ALL" triggers for other actions outside of the one that is firing?
>
> Hi Rick,
>
> Yes. Disabling a trigger means that it also won't fire if another user
> happens to update the table at that time.
>
> >Also my biggest issue is I am in a insert trigger and it is firing an update
> >trigger when I don't want it to. If I disable triggers while in a trigger
> >will there be any issues?
>
> You can use TRIGGER_NESTLEVEL(OBJECT_ID('triggername')) to check if the
> trigger is called, directly or indirectly, from the specified trigger.
> Or you can use TRIGGER_NESTLEVEL() to check if the trigger is called
> from ANY trigger.
>
> Another possible workaround is to add a dummy column to a table and
> include that dummy column in updates that stem from a trigger. In the
> UPDATE trigger, you can then use IF UPDATE(DummyColumn) to check if the
> update comes from another trigger or not. Note that this is only
> reliable if you can control all update statements fired at the database,
> so if anyone has the possibility to write his or her own queries, this
> is not safe.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...
>