Question on generating INSERT statements in a trigger

I'm referencing this article: http://decipherinfosys.wordpress.com/2007/09/27/ddl-triggers-in-sql-server-2005/

To get the entire insertion command text you use this: SELECT EVENTDATA().value((/EVENT_INSTANCE/TSQLCommand/CommandText)[1],nvarchar(max)). This returns

Refer to the article above for examples if this is unclear.

MSDN article for EVENTDATA function: http://msdn.microsoft.com/en-us/library/ms173781.aspx

Sorry,
EVENTDATA is for DDL triggers only.

Look at this forum post for the equivalent for DML triggers: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3576300&SiteID=1&pageid=0

I'm trying this now in my trigger and when I insert a row into table dbo.users,  all I see in the results is (no column name), and NULL in row 1.

CREATE TRIGGER [dbo].[t_SWITCHBOARD]
ON [dbo].[users]
FOR INSERT
AS
	SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

Select allOpen in new window

Hello,

The solution above won't work for you as it's a DDL trigger, meaning that it will fired on database structure change only, not when you insert/update/delete data (whose trigger is a DML trigger).

The easiest way to achieve your goal is to run a continuous sql profiler session that stores the logs in a table. In the profiler session select 'Save to Table' and setup a table to hold the data, select the Event 'SQL Statement Completed', select the Column TextData, and filter columns on TextData like 'INSERT INTO%'.

Look at this article if you don't know how to start the profiler : http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1159433,00.html

regards,
Abdel

Interesting.. Those DDL triggers could be useful as an aside for tracking and managing revisions on the database itself. That's a whole other question I now have insight into :)

I'm not sure the profiler is available in SQL Server 2005 Express, which I should have mentioned I'm using.

I can create a trigger for you to dynamically determine the table name and column names, and then write out an INSERT statement from that data, but it's not exactly the instert statement that was run.

Wouldn't it be easier to just log the data from the inserted table?

Makolyte - your 2nd link using the DML trigger:

      select t.text from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) t where r.session_id = @@Spid

 I end up with a result that is equal to the text of the trigger itself.

aaronakin : "Wouldn't it be easier to just log the data from the inserted table?"

Could you please ellaborate?

Basically what I'm trying to accomplish here is that I have a remote SQL Server 2005 Standard edition instance running on another computer. I have a linked server setup to it that is functioning.   The local computers are running Express edition - no agent, but I want the Express edition instances to be able to copy certain inserted and updated data over to the remote instance so I was toying with the idea of, on each necessary table's insert/update, I would create a string which represented the insert/update/delete statement necessary. A local table would hold this list of SQL statements that needed to be executed on the linked server.

Sounds like log shipping. :-)

I'll see if I can come up with a trigger for you.

in a sense, yes, only the "parent" server (the 2005 Standard edition instance) is really aggregating data from all of the child servers (2005 Express) -  values are inserted, things are changed, and the parent needs to become aware of all of that in near real time.

.... and the children should push to the parent, the parent should not pull from them.

If that's the case, I'd recommend using Replication, which is available to Standard and Express editions.  This is going to be your BEST course of action.

http://msdn.microsoft.com/en-us/library/ms165700(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms165713(SQL.90).aspx

I explored that earlier, however, SQL Server Express instances can only subscribe to replication publications from the server (standard edition.) That is the "wrong direction" as it were.

True, but you could make your parent server the publisher and the child servers the subscribers.  I realize this seems opposite from what you are wanting, but with Merge replication, changes are bi-directional, meaning that the changes made to the subscribers get replicated back to the publisher.

Check out this article (http://www.databasejournal.com/features/mssql/article.php/3719781).  It talks about this particular issue in the last few paragraphs.

Hope this helps.

interesting, I'll explore that further.. Still interested in seeing a trigger based on my initial question though. :)

curious - I'm reading more about that replication you suggested.. it sounds like it might work to keep all of the database instances in synch, which is not necessarily what i want. ie. all of the children do not need to know about data collected by other children.. If the children were all subscribers of this parent publication to allow data to be updated regardless where it changed, wouldn't that imply all of the children would all share the same data?

Interesting..   so, the database server itself, is, in effect, a database itself... very cool
thanks!

aaronakin - quick question about this.

how do I change those last few lines:

so that the result goes into a variable rather than just output?

  SELECT @SQL = 'SELECT Query_IdentOn + Query + '''+ @ColumnsTypes +'); '' + Query_IdentOff FROM ##i'
 
  EXEC(@SQL)

Select allOpen in new window

I was able to modify the @SQL =  statement to include the declaration of an output variable, as well as the code to insert that into a new table which solved my need.

it did seem a bit convoluted though but I guess in terms of a dynamically generated SQL statement, it makes sense..

aaronakin - I am having a couple problems with the trigger you wrote, are you still available in this thread for a followup or should I post a new question that refers to this thread?

regards