SQL Server 2k Insert Trigger - Output to file

[MikeHurley]

Hi All

Does anyone know if its possible to setup an Insert Trigger to output the inserted row to a csv file?

Thanks

[exterminator]

Hey Mike, Welcome to CG!Does anyone know if its possible to setup an Insert Trigger to output the inserted row to a csv file?About triggers there could be specifics for each rdbms. What are you using? I am not sure about MS SQL Server but in Sybase SQL Server there are inserted and deleted tables generated that you can use from within the triggers since all triggers in case of Sybase are after triggers (probably the same holds true for MS SQL as well but definitely not for Oracle). In the inserted table are the records that were inserted (the latest value with which you update the table) newly by the query and in the deteled are the records that were replaced/deleted by the query. In case of Insert statement - data would only be in the inserted table. In case of update - data would be in both inserted (new values) and deleted (old values) and in case of delete - data would only be in the deleted table.

Then you can use the "bcp" command to get the text file output with delimiter seperated values. You might need to look at the details of bcp before using it and the various options that are available to you for that (you may find these in your particular database reference manual).

As for MS SQL Server - visit this link it - bcp using format files (http://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.html). MSDN also provides good information over the bcp. For MS SQL server - probably they also create the two temporary tables named inserted/deleted that you can use from within the triggers. Let me know in case I confused you somewhere. Hope this helps. Regards.