Can a trigger create a text file

  • 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!

  • 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]

  • 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


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Yes, I need the text file for another system by another developer with me in the middle.

  • 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


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • 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.

  • 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