Possible to use a trigger to write a file to disk?

Question

Possible to use a trigger to write a file to disk?

kumaichi

SSC Veteran

Points: 263
More actions
March 12, 2004 at 8:41 am

#84716

Hi All,
I'm having a difficult time designing a solution to a problem I've been given and was wondering if anyone has some possible insight they could share.
I've been given the task to write out a file whenever a column in our database changes. Basically, an action happens on a record and I need to write out an XML file with all of the data of that row. This is a customer requirement and I'm having a difficult time with it. My first idea was to create a trigger on the column that will be the notification column. This worked fine.
I then tried to tackle writing out a file in the trigger, I couldn't figure out if this is even possible. So, I thought maybe I could write an Extended Stored Procedure. My thinking was, I could create the xml file in the trigger by concatinating a bunch of strings and then, pass that string as an ntext into the extended stored procedure. This has it's own problems because I couldn't figure out how to create an ntext object to pass into my extended stored procedure.
If anybody has any ideas how I could overcome this problem, it would be greatly appreciated.
Thanks,
Craig

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

You can use the sp_makewebtask system stored procedure for this; read about it in BOL. You'll probably want to use a template file.

Using a trigger for something like this incurs some potential issues with performance, locking, and rollbacks, of course.

--Jonathan

kumaichi SSC Veteran Points: 263 More actions · Answer 2

Jonathan,

Thanks for the response. I didn't know that system sp even existed, very cool. After reading the books online, it looks like I can create a job that calls this sp and create my file. Is there a way to schedule a job to run when something happens in the database? My problem is that the customer wants it to be real time, so when the column is updated from '5' to '6' they want me to create the file with the rest of the data .

Is it possible to do this real time without using a trigger?

Thanks again for your help with this.

Have a great day,

Craig

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

This proc is essentially real-time by default; it will be executed immediately unless you include an optional @whentype other than 1. The fun of using this proc in a trigger will be figuring out how to get all the rows from the inserted table into the @query...

It's interesting that you brought up the subject of jobs, as that's how nasty stuff like this is usually disassociated from a trigger. One uses the trigger to write the relevant information to a table and then a job runs every x minutes to run the proc against each row in that table and then delete the row. Not real-time, but safer and faster.

--Jonathan