How to get the whole DDL command from EVENT_INSTANCE

Hello,
I try to save the current DDL in a table using the trigger on database ddl
events.
As usual,
DECLARE @.data XML
SET @.data = EVENTDATA()
@.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
How can I extract more then 2000 chars? Should I use a system table or
function to retrieve all the DDL command? I have SPs whith tons of chars...
Thanks,
CatalinHow about, for instance:
@.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:46BD42B9-FA1B-4697-BD7D-2E54F52F4B93@.microsoft.com...
> Hello,
> I try to save the current DDL in a table using the trigger on database ddl
> events.
> As usual,
> DECLARE @.data XML
> SET @.data = EVENTDATA()
> @.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
> How can I extract more then 2000 chars? Should I use a system table or
> function to retrieve all the DDL command? I have SPs whith tons of chars..
.
> Thanks,
> Catalin|||Thanks, but i have SPs with probably 40k chars or more... Neither varchar
(8000) is enough...
"Tibor Karaszi" wrote:

> How about, for instance:
> @.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)')
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in messag
e
> news:46BD42B9-FA1B-4697-BD7D-2E54F52F4B93@.microsoft.com...
>|||Did you try nvarchar(max)?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:989EED5F-D2F0-44EB-A43E-A2FB23BB9B5B@.microsoft.com...
> Thanks, but i have SPs with probably 40k chars or more... Neither varchar
> (8000) is enough...
> "Tibor Karaszi" wrote:
>|||Thank you, I had no ideea about (max) implementation on 2K5... (Please, don'
t
tell me that it was also available on SQL 2000...)
I am so deceived about me... After 8 years of SQL I will have to start again
from ABC... Sometimes I am so busy to find complex solutions and I am not
able to see the simplest one.
Thanks again|||> Thank you, I had no ideea about (max) implementation on 2K5... (Please, don'ted">
> tell me that it was also available on SQL 2000...)
The max datatypes are indeed new to 2005. Consider them as replacements for
the less than user
friendly text, ntext and image datatypes.

> I am so deceived about me... After 8 years of SQL I will have to start aga
in
> from ABC... Sometimes I am so busy to find complex solutions and I am not
> able to see the simplest one.
This happens to all of us. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:01DD8998-1C93-43E2-AA9A-F82694049866@.microsoft.com...
> Thank you, I had no ideea about (max) implementation on 2K5... (Please, do
n't
> tell me that it was also available on SQL 2000...)
> I am so deceived about me... After 8 years of SQL I will have to start aga
in
> from ABC... Sometimes I am so busy to find complex solutions and I am not
> able to see the simplest one.
> Thanks again

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,
Pieter
See if this helps:
http://vyaskn.tripod.com/tracking_sq...y_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_sq...y_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 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_sq...y_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...
>
>

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_s...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_s...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 wa
s
> an insert/update or delete-statement (when having 1 trigger that handles a
ll
> 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 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_s...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...
>
>

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
> >
> >
> >
>
>

How to get the SQL statement that caused the trigger fire ?

Hi:
Is it possible to grab the full text of the sql statement that caused a
trigger on a table to fire ?
I try to put the DBCC INPUTBUFFER(@.@.SPID) into the 1st line of the
delete trigger, and in Quary analyzer i'm able to see the DBCC
INPUTBUFFER result, but i don't know how to retrieve this result in my
application code (using ADO and ADO.Net).
Please help.
Thanks
JCVoonHi,
why not look at the trigger definition onthe table...that will tell you
under what conditions the trigger will fire....
"jcvoon" wrote:

> Hi:
> Is it possible to grab the full text of the sql statement that caused a
> trigger on a table to fire ?
> I try to put the DBCC INPUTBUFFER(@.@.SPID) into the 1st line of the
> delete trigger, and in Quary analyzer i'm able to see the DBCC
> INPUTBUFFER result, but i don't know how to retrieve this result in my
> application code (using ADO and ADO.Net).
> Please help.
> Thanks
> JCVoon
>

How to get the SQL statement that caused the trigger fire ?

Hi:
Is it possible to grab the full text of the sql statement that caused a
trigger on a table to fire ?
I try to put the DBCC INPUTBUFFER(@.@.SPID) into the 1st line of the
delete trigger, and in Quary analyzer i'm able to see the DBCC
INPUTBUFFER result, but i don't know how to retrieve this result in my
application code (using ADO and ADO.Net).
Please help.
Thanks
JCVoonHi,
why not look at the trigger definition onthe table...that will tell you
under what conditions the trigger will fire....
"jcvoon" wrote:
> Hi:
> Is it possible to grab the full text of the sql statement that caused a
> trigger on a table to fire ?
> I try to put the DBCC INPUTBUFFER(@.@.SPID) into the 1st line of the
> delete trigger, and in Quary analyzer i'm able to see the DBCC
> INPUTBUFFER result, but i don't know how to retrieve this result in my
> application code (using ADO and ADO.Net).
> Please help.
> Thanks
> JCVoon
>

How to get the last Query executed?

Hello, i have a trigger and i want to know the query that raised it, or want to retrieve the last executed query by the server. I think it's a hard question but i know that someone can help me... ThanksOriginally posted by parmaia
Hello, i have a trigger and i want to know the query that raised it, or want to retrieve the last executed query by the server. I think it's a hard question but i know that someone can help me... Thanks

Fire up SQL Profiler and run a trace with the filters configured to limit it to the table in question (object).|||if object_id('dbo.test') is not null
drop table dbo.test
go
create table dbo.test (f1 int null)
go
create trigger dbo.trgIUD_test on dbo.test for insert, update, delete as
dbcc inputbuffer (@.@.spid)
go
insert dbo.test values (1);
go
drop table dbo.test
go|||Originally posted by Steve Duncan
Fire up SQL Profiler and run a trace with the filters configured to limit it to the table in question (object).

Hey, i didn't know that exists, thank you for your help!, has very very use full!

How to get the client IP Address in T-SQL ?

Hi,

My problem is -
I have a trigger for auditing the changes(insert/update/delete) in the database table.That is done and is working fine. But I need to have the client's IP address from where the changes are done. That I need in T-SQL, that means, not in any web form but in the SQL/T-SQL.

As I have checked many forums, I got that there is extended stored procedure in master database named xp_cmdshell which has xplog70.dll and when we execute this stored procedure with 'ipconfig' we can get the IP Address. But I do not need that in master database. I need that in my database say myDB.

So how to proceed further. I don't know whether to create extended SP which contains DLL or is there any other option.

Pls help
Thanks in advanceHave you tried calling the stored procedure* from within your "myDB"? :)|||See this the stored procedure code and call,

create Procedure sp_get_ip_address (@.ip varchar(40) out)
as
begin
Declare @.ipLine varchar(200)
Declare @.pos int
set nocount on
set @.ip = NULL
Create table #temp (ipLine varchar(200))
Insert #temp exec master..xp_cmdshell 'ipconfig'
select @.ipLine = ipLine
from #temp
where upper (ipLine) like '%IP ADDRESS%'
if (isnull (@.ipLine,'***') != '***')
begin
set @.pos = CharIndex (':',@.ipLine,1);
set @.ip = rtrim(ltrim(substring (@.ipLine ,
@.pos + 1 ,
len (@.ipLine) - @.pos)))
end
drop table #temp
set nocount off
end
go

declare @.ip varchar(40)
exec sp_get_ip_address @.ip out
print @.ip

But this is in the master Database.If I do this same thing in my database say myDB it gives error that it do not have extended SP|||Try

Exec dbo.sp_get_ip_address @.ip out|||If we exec this stored procedure in the trigger, How to store the output in the table or in some variable. Since I have tried this way - Insert #temp exec master..xp_cmdshell 'ipconfig' which gives error saying that -' insert and execute statments cannot be nested'. And if I first create the table with a column and then write - select * into #temp from exec sp_get_my_ip_address @.ip out, it gives error as incorrect syntax near exec|||Let's just confirm the DBMS that we're using...
I'm guessing SQL Server 2000, but please correct me if I'm wrong.

I'll then move the thread to the appropriate topic.|||I am using SQL Server 2005|||This works in 2000 and 2005 and might be of some use to you - let me know how you get on.

DECLARE @.host varchar(255)
SET @.host = host_name()

CREATE TABLE #Results (
Results varchar(255)
)

DECLARE @.cmd varchar(260)
SET @.cmd = 'ping ' + @.host

INSERT INTO #Results
EXEC master..xp_cmdshell @.cmd

SELECT Replace(Left(Results, CharIndex(']', Results)), 'Pinging ', '') As [client]
, host_name() As [host_name()]
FROM #Results
WHERE Results LIKE 'Pinging%'

DROP TABLE #Results|||Now my problem is - if the client machine do not have host name assigned in that case how can we ping and get the IP ?|||You can't...
Back to your method it is then...

Try this

--DROP trigger and/or table if they exist
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'TR' AND name = 'myTable_InsertUpdate') BEGIN
DROP TRIGGER myTable_InsertUpdate
END
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'myTable') BEGIN
DROP TABLE myTable
END

--Create out table; note the audit fields
CREATE TABLE myTable (
id int PRIMARY KEY NOT NULL IDENTITY(1,1)
, field1 char(1)
, changed_by_ip char(15)
, changed_by_host char(15)
, datetime_changed datetime
)
GO

--Create trgger for update and insert
CREATE TRIGGER myTable_InsertUpdate
ON myTable
FOR insert, update
AS
DECLARE @.ipLine varchar(255)
DECLARE @.pos int
DECLARE @.ip char(15)

--temporary table creation
CREATE TABLE #ip (
ipLine varchar(255)
)

--Insert the return of ipconfig into the temp table
INSERT #ip EXEC master..xp_cmdshell 'ipconfig'

--find the line which contains the IP and assign it to a variable
SET @.ipLine = (
SELECT ipLine
FROM #ip
WHERE ipLine LIKE '%IP Address%'
)

--If the IP is known
IF Coalesce(@.ipLine, '***') <> '***' BEGIN
--Find the index of the colon from the END of the string
SET @.pos = CharIndex(':', Reverse(@.ipLine), 1) - 1
--Trim the IP off the end of the string
SET @.ip = Right(@.ipLine, @.pos)
--Remove any trailing or leading white space
SET @.ip = RTrim(LTrim(@.ip))
END

--Drop the temp table
DROP TABLE #ip

--Update the audit fields based on the value being updated
UPDATE myTable
SET changed_by_ip = @.ip
, datetime_changed = GetDate()
, changed_by_host = host_name()
WHERE id IN (SELECT id FROM inserted)
GO

--Insert some test values
INSERT INTO myTable (field1) VALUES ('a')
INSERT INTO myTable (field1) VALUES ('a')
--Display initial values
SELECT * FROM myTable

--Update one of the fields
UPDATE myTable
SET field1 = 'b'
WHERE id = 2
--Display changed values.
SELECT * FROM myTable

--Notice the change in datetime_changed where id = 2
GO

--And finally; clean up after ourselves
DROP TRIGGER myTable_InsertUpdate
DROP TABLE myTable

This works on my install of 2000 and 2005.|||Let me see if I understand what the original poster (neetu bhagtani) was looking for, because this line of reasoning doesn't sound correct to me.

You have at least a SQL 2005 server, and a web server in a data center. You have clients that connect to the web server using HTTP, but those clients do not log in to the SQL Server directly.

If I've described the configuration that you've currently got, then your SQL Server can only get the client machine's IP address from the web server, because the SQL Server only "sees" the web server via TCP/IP, it never deals directly with the client so the SQL Server won't know the IP address of the client.

-PatP|||See as I told you before I get an error at this line -

INSERT #ip EXEC master..xp_cmdshell 'ipconfig' (as per the code given by you)

Insert #temp exec master..xp_cmdshell 'ipconfig' (and as per the code written by me)

which gives error saying that -' insert and execute statments cannot be nested'.

Also, I would like to tell that I was working on testing environment means on local server but not on live server since it this works on testing server and only we can upload and test on live server. Live Server is SQL Server 2003

Now when I uploaded your pinging version of Stored Procedure I get an error telling that master DB owner is someone else and when I give '[dbo].master' for executing the xp_cmdshell it gives another error saying that server is not the sysservers list use 'sp_addlinkedserver' Stored Procedure to add the server in the sysservers list

Pls help|||Yes, as described below is very much true

If I've described the configuration that you've currently got, then your SQL Server can only get the client machine's IP address from the web server, because the SQL Server only "sees" the web server via TCP/IP, it never deals directly with the client so the SQL Server won't know the IP address of the client.|||So all users will appear to have the same IP... (which kinda ruins what you're trying to achieve, no?)|||Some users will access the application on web server from UK, some from US, and some from India. So all will connect to same web server on which the application resides but will have different IP addresses of their machines.|||You do realize that the IIS logs keep all of the information you are after. Right?|||You should be handling this at the application level and/or analyzing logs as MCrowley suggests. It is not physically possible to do at the database level per Pat's assertion.

Also, if you enabled the ability to fire xp_cmdshell under the privileges that your web app uses, I suggest you disable it right now.|||But how to exactly do that ? how to get it from IIS log ?|||Let's go back to the original requirement for a second. You need to get the webserver client's IP address, so you can put that into an audit trail for any updates. Is this about what you need?|||See I am inserting the data in my Audit table in the trigger while insert/update/delete and I have the log file created by IIS which has all IP Addresses. My problem is how to get the IP from log file and insert in the table since insert in the table is done in the trigger while insert/update/delete|||This sounds like it should be done by passing the variable from the client-side.
For example, if you're users are accessing the databse through a web front end then pick up the value from the client's workstation and pass it on submit of a query as one of the values.

Make sense?|||Why would this need to be done by a trigger? Why can't the webserver pass this information to you? Are there users accessing this data by methods other than the webserver?|||Hi there,

Does it have to be IP address? will hostname do? with a lookup table for IP address or something.

If you want the hostname then do this:

select hostname from sysprocesses
where spid = @.@.spid|||True, but in an IIS implementation both the web server's address and hostname ought to be constants. The original poster wants the IP address of the client, which is not available to the SQL Server.

-PatP

how to get tablename in CLR-Trigger?

Hi,
i want to write an 'universal' audit trigger in C# or VB. For that i need
the tablename. But i found nothing in SqlContext.TriggerContext.
How can i get this tablename?
thanks,
HelmutHelmut
Table name of what? of trigger create on this table?
CREATE TABLE TT
(
COL INT
)
CREATE TRIGGER MY_TR ON TT
FOR INSERT
AS
DECLARE @.ObjID int
SET @.ObjID = (SELECT parent_obj FROM sysobjects WHERE id = @.@.PROCID)
SELECT OBJECT_NAME(@.ObjID) AS 'Parent Table'
INSERT INTO TT VALUES (1)
SELECT * FROM TT
DROP TABLE TT
"Helmut Woess" <user22@.inode.at> wrote in message
news:9serddu999is$.evazv4rhvdim.dlg@.40tude.net...
> Hi,
> i want to write an 'universal' audit trigger in C# or VB. For that i need
> the tablename. But i found nothing in SqlContext.TriggerContext.
> How can i get this tablename?
> thanks,
> Helmut|||Uri,
i need this in an assembly, not in TSQL. So @.@.PROCID is not available.
thanks, Helmut|||On Fri, 26 May 2006 11:06:36 +0200, Helmut Woess wrote:

>Hi,
>i want to write an 'universal' audit trigger in C# or VB. For that i need
>the tablename. But i found nothing in SqlContext.TriggerContext.
>How can i get this tablename?
Hi Helmut,
As far as I know, you have to specify a single tablename in the Target
property of the SqlTrigger attribute - so that rules out your idea of a
universal trigger. You'll have to use one per table.
Hugo Kornelis, SQL Server MVP|||Am Sat, 27 May 2006 01:47:20 +0200 schrieb Hugo Kornelis:

> As far as I know, you have to specify a single tablename in the Target
> property of the SqlTrigger attribute - so that rules out your idea of a
> universal trigger. You'll have to use one per table.
Yes, you are right, but in the moment it isn't necessary to set this
properties and i can use one assembly for many triggers. I hope this will
be possible in future versions of SqlServer too, i don't see any advantage
in the target properties. Can you call me one?
Helmut|||On Sat, 27 May 2006 11:33:45 +0200, Helmut Woess wrote:
(snip)
> i don't see any advantage
>in the target properties. Can you call me one?
Hi Helmut,
Sorry for the delayed reply - I haven't had much chance to play with CLR
triggers before now.
Earlier today, I stumbled over a web page stating that the target
properties for a CLR trigger are only used to aid Visual Studio when you
use the Deploy Project menu option. If you deploy manually, they have no
effect. Unfortunately, I didn't save the URL.
Hugo Kornelis, SQL Server MVP