December 1, 2008 at 7:19 am
Hai,
We used the below DDL Trigger during the table alter and we are able to get the T-SQL command/ statement from eventdata().
ALTER TRIGGER trPreventTblChange
ON DATABASE
FOR ALTER_TABLE
AS
DECLARE @data XML
SET @data = EventData()
INSERT EvtLog (PostTime, LoginName, EventType, TSQLCommand,Data)
VALUES
(GETDATE(),
CONVERT(NVARCHAR(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),CONVERT(NVARCHAR(4000),@Data) ) ;
GO
Is there any method to get the T-SQL command/ statement using DML triggers?.
Ie. While we insert a record using INSERT Statement, is there any possible way to get the T-SQL Statement using DML trigger for AFTER INSERT
Thanks in advance
Regards,
K.A.Maheshkumar
December 1, 2008 at 10:44 am
TO my knowledge there is no way to get the SQL statement executed from within a DML trigger. If you need to log SQL statements you would need to use a server-side trace to log the activity to a trace file which you could then load into a table to manipulate, if you want. Look Up server side trace in BOL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 1, 2008 at 11:15 am
If you had the rights, and could dbcc inputbuffer your SPID, that might work. But it doesn't always get you everything.
What are you trying to accomplish here? Figure out where the change came from?
December 1, 2008 at 11:22 pm
Hai,
Steve Jones, Thanks for your reply.
We tried u r method and get the SQL statement for insert,delete,update.
Regards,
K.A.Maheshkumar
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply