|
|
Posted by Erland Sommarskog on 05/02/07 21:29
Ots (otsmcgee@yahoo.com) writes:
> I'm using SQL 2000, which is integrated with a VB.NET 2003 app.
>
> I have an Audit trigger that logs changes to tables. I want to apply
> this trigger to many different tables. It's the same trigger, with the
> exception of the table name. I could manually change the table name in
> the trigger and create it, over and over, but I'd like to automate
> this - by iterating through the collection of tables and passing the
> tablename to something that would create the audit trigger for that
> table.
>
> Any ideas? With any scripting language I can create the text of the
> CREATE TRIGGER procedure for each table and use simple text
> replacement, but how do I automate that inside of SQL?
I don't see why you would use T-SQL to generate the triggers? Why not
simply use a scripting language of your choice for the job.
Besides I don't get a good feeling when you say that it is the same
trigger, save for the table name. That indicates that you are looping
over the column set, use a lot of dynamic SQL. It's not going to be
healthy for the performance of your system. It would be a better idea
to that looping in the trigger-generator, so that the trigger code
is static SQL only.
An even better idea may be to invest which third-party products
that could meet your needs. ApexSQL has a trigger-based product
SQLAudit. There are also auditing solutions that works from the
transaction long, Lumigent has one I know.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|