October 5, 2007 at 12:43 am
Hi!
When a new record is added to a table, can a trigger create a text file for the new record? Is this possible? If not, is there some other way to automatically export a text file from SQL Server each time a new record is added?
Thanks for any help!
October 5, 2007 at 2:03 am
The short answer would be yes it's possible from a trigger, but it's not something I would allow any of my developers to implement. The risk of failed transactions and poor performanceis just too big.
Why do you want a text file every time a record is inserted? Is this for auditing or do you need them as input for antoher system? In the latter case service broker might be the best option.
Markus
[font="Verdana"]Markus Bohse[/font]
October 5, 2007 at 2:09 am
sivicako (10/5/2007)
Hi!When a new record is added to a table, can a trigger create a text file for the new record? Is this possible? If not, is there some other way to automatically export a text file from SQL Server each time a new record is added?
Thanks for any help!
You could do something like:
CREATE TRIGGER triggerOnMyTable ON myTable
AFTER INSERT
AS
BEGIN
DECLARE @query NVARCHAR(1000)
DECLARE @outText NVARCHAR(100)
SELECT @outText = 'inserted ' + CAST(a AS VARCHAR)
FROM inserted
SET @query = 'master..xp_cmdshell ''echo ' + @outText
+ ' > c:\file.txt'' '
EXEC ( @query )
END
But this is wrong!
1: If the transaction in which the row has been inserted is rolled back, your file will still contain the data
2: Concurrency issues
You could write a CLR trigger, but such assemblies will need to be given external access.
You would also have similar problems with transactions.
There may of course be other solutions
Regards,
Andras
October 5, 2007 at 2:20 am
Yes, I need the text file for another system by another developer with me in the middle.
October 5, 2007 at 2:24 am
You may want to look at the Service Broker. It will allow you to send the inserted data to a queue which you can process sequentially and transactionally. This would avoid the problems mentioned above, but it is relatively more difficult to set up.
Andras
October 5, 2007 at 2:42 am
Thanks Old Hand!
I think the roll back would be a minor issue in my case. My concern is more about real time data updates from the SQL server that will be used by another system. Once that is achieved, concurrency issues would become, I hope, irrelevant or negligible.
I will try your suggestion.
October 5, 2007 at 9:35 am
Don't do this. Spawning processes in a trigger is an extremely bad idea.
Use Service Broker as mentioned in this thread. http://www.sqlservercentral.com/Forums/Topic403678-361-2.aspx
I'm trying to get someone to write a nice basic article on this.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy