Friday, March 30, 2012

How to get the transaction in the trigger?

Hi,
I want to be able to recuperate in my Trigger the transaction that is
'triggering' the Trigger. So i want to be able to have the SQL-statement
that was/will be performed on the table.
Does anybody knows how to do this? any help would be really appreicated!
thanks a lot in advance,
PieterSee if this helps:
http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23hlysmzRFHA.1392@.TK2MSFTNGP10.phx.gbl...
Hi,
I want to be able to recuperate in my Trigger the transaction that is
'triggering' the Trigger. So i want to be able to have the SQL-statement
that was/will be performed on the table.
Does anybody knows how to do this? any help would be really appreicated!
thanks a lot in advance,
Pieter|||Thanks!! You're great!!
I modied it a little bit so it puts everything in a table.
just one more thing: is there a way to know in the trigger itself if it was
an insert/update or delete-statement (when having 1 trigger that handles all
like in your exemple).
Thanks a lot!
Pieter
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:umvn0uzRFHA.3944@.TK2MSFTNGP10.phx.gbl...
> See if this helps:
> http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:%23hlysmzRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to be able to recuperate in my Trigger the transaction that is
> 'triggering' the Trigger. So i want to be able to have the SQL-statement
> that was/will be performed on the table.
> Does anybody knows how to do this? any help would be really appreicated!
> thanks a lot in advance,
> Pieter
>
>|||Just check the whether you have >= 1 rows in the inserted and deleted tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:e2%23oHE0RFHA.3076@.TK2MSFTNGP14.phx.gbl...
> Thanks!! You're great!!
> I modied it a little bit so it puts everything in a table.
> just one more thing: is there a way to know in the trigger itself if it was
> an insert/update or delete-statement (when having 1 trigger that handles all
> like in your exemple).
> Thanks a lot!
> Pieter
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:umvn0uzRFHA.3944@.TK2MSFTNGP10.phx.gbl...
>> See if this helps:
>> http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm
>> --
>> HTH,
>> Vyas, MVP (SQL Server)
>> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> news:%23hlysmzRFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I want to be able to recuperate in my Trigger the transaction that is
>> 'triggering' the Trigger. So i want to be able to have the SQL-statement
>> that was/will be performed on the table.
>> Does anybody knows how to do this? any help would be really appreicated!
>> thanks a lot in advance,
>> Pieter
>>
>|||See if the following helps:
IF EXISTS(SELECT 1 FROM inserted) AND (NOT EXISTS (SELECT 1 FROM deleted))
BEGIN
PRINT 'INSERT'
--Perform necessary processing
END
ELSE
IF EXISTS (SELECT 1 FROM deleted) AND (NOT EXISTS (SELECT 1 FROM inserted))
BEGIN
PRINT 'DELETE'
--Perform necessary processing
END
ELSE
IF EXISTS(SELECT 1 FROM inserted) AND (EXISTS(SELECT 1 FROM deleted))
BEGIN
PRINT 'UPDATE'
--Perform necessary processing
END
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:e2%23oHE0RFHA.3076@.TK2MSFTNGP14.phx.gbl...
Thanks!! You're great!!
I modied it a little bit so it puts everything in a table.
just one more thing: is there a way to know in the trigger itself if it was
an insert/update or delete-statement (when having 1 trigger that handles all
like in your exemple).
Thanks a lot!
Pieter
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:umvn0uzRFHA.3944@.TK2MSFTNGP10.phx.gbl...
> See if this helps:
> http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:%23hlysmzRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to be able to recuperate in my Trigger the transaction that is
> 'triggering' the Trigger. So i want to be able to have the SQL-statement
> that was/will be performed on the table.
> Does anybody knows how to do this? any help would be really appreicated!
> thanks a lot in advance,
> Pieter
>
>|||Thanks, it works great like that.
Unless users perform an Update/Delete that doesn't update or delete a record
at all, but I don't need that records neither. Instead it's maybe better
form e, hehe :-)
thanks a lot for your work! You helped me a lot with this problem!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:e3y6io0RFHA.1500@.TK2MSFTNGP09.phx.gbl...
> See if the following helps:
> IF EXISTS(SELECT 1 FROM inserted) AND (NOT EXISTS (SELECT 1 FROM deleted))
> BEGIN
> PRINT 'INSERT'
> --Perform necessary processing
> END
> ELSE
> IF EXISTS (SELECT 1 FROM deleted) AND (NOT EXISTS (SELECT 1 FROM
inserted))
> BEGIN
> PRINT 'DELETE'
> --Perform necessary processing
> END
> ELSE
> IF EXISTS(SELECT 1 FROM inserted) AND (EXISTS(SELECT 1 FROM deleted))
> BEGIN
> PRINT 'UPDATE'
> --Perform necessary processing
> END
>
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:e2%23oHE0RFHA.3076@.TK2MSFTNGP14.phx.gbl...
> Thanks!! You're great!!
> I modied it a little bit so it puts everything in a table.
> just one more thing: is there a way to know in the trigger itself if it
was
> an insert/update or delete-statement (when having 1 trigger that handles
all
> like in your exemple).
> Thanks a lot!
> Pieter
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:umvn0uzRFHA.3944@.TK2MSFTNGP10.phx.gbl...
> > See if this helps:
> > http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:%23hlysmzRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I want to be able to recuperate in my Trigger the transaction that is
> > 'triggering' the Trigger. So i want to be able to have the SQL-statement
> > that was/will be performed on the table.
> >
> > Does anybody knows how to do this? any help would be really appreicated!
> >
> > thanks a lot in advance,
> >
> > Pieter
> >
> >
> >
>
>

No comments:

Post a Comment