Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Question Unanswered: Writing records to text file through trigger

    I have a table with following fields

    [ADD1][ADD2]
    2 4

    there is trigger on this table for insert update and delete when ever any of these three operations happen on table trigger will fire and write

    output to text file abc.txt which is located in c: drive

    I don't know how to do this at all.... pls help

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Don't even try it. It would be very unwise to do that in a trigger because of multi-user considerations and because the data in the file would be inaccurate anyway. Remember that a trigger sees uncommitted data but it would be hard to roll back changes to a file. Also, you will very likely create an undetectable deadlock and halt your system.

    Please explain A) Why you need this data in a text file, and B) how often the file needs to be updated.

    If you want updates in real time then it would be better to change whatever process consumes the data so that it reads the table instead of the file. If you want an audit trail then take a look at the Change Data Capture feature in SQL Server 2008.

  3. #3
    Join Date
    Aug 2009
    Posts
    73

    Question Writing to text file through trigger

    Well we are working with Audit database and every time someone deletes it or updates the record or inserts a record trigger will fire and store all info in a seprate table, but the size of database is growing very fast as insert update and delete are going every second we r new to SQL server.,...... so can we write records to text file or what will be the best strategy i have no idea how to come up with strategy

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Writing to a text file is not the solution. It's not likely to save you much space anyway.

    I'd concentrate on trying to optimise the existing audit process. Look at what is consuming the space and maybe be more selective about the data you capture. If you are using SQL Server 2008 Enterprise then consider using the Change Data Capture feature instead of triggers.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Also have a look at your recovery model. Transaction logs can get real unwieldy, real quick.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    write to an audit table, just roll data out at whatever interval.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by amitwadhawan123 View Post
    Well we are working with Audit database and every time someone deletes it or updates the record or inserts a record trigger will fire and store all info in a seprate table, but the size of database is growing very fast
    So get more drive space or start archiving old data.
    Writing to a text file with a trigger is a bad idea.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Aug 2009
    Posts
    73

    Suggestions for table audits

    is there any other better way other then increasing the diskspace. what set up is in your company for the audits. Do you have some suggestions regarding the table strategy for the audit tables.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Records are going to take up as much space in files as they will in your database, so drive space is a wash. Get more if you need it.

    Here is a script that generates a script for creating archive tables and supporting triggers:
    http://dl.dropbox.com/u/2507186/Scripts/AutoArchive.sql
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •