One trigger, many tables - how to automate?

[Ots]

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?

Thanks.

[Erland Sommarskog]

Ots (ot******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

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

Go tell the lawyers and the accountants that the audit data is being
kept in the same schema as the data being audited. They will want to
put that in the "reason for termination" in your personnel file.

Get a third party audit tool that will run outside the schema and trap
things as they cross the system boundary. This data should then be
sent to physically separate data store, so that if (read: WHEN) the
database is trashed or crashes, you can still reconstruct the audit
trail. Do you keep your backup on the same hard drive as the
database? No! Of course not! This is the same thing.

[Ed Murphy]

--CELKO-- wrote:

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

Go tell the lawyers and the accountants that the audit data is being
kept in the same schema as the data being audited. They will want to
put that in the "reason for termination" in your personnel file.

Get a third party audit tool that will run outside the schema and trap
things as they cross the system boundary. This data should then be
sent to physically separate data store, so that if (read: WHEN) the
database is trashed or crashes, you can still reconstruct the audit
trail. Do you keep your backup on the same hard drive as the
database? No! Of course not! This is the same thing.

This depends on how strict the audit needs to be, and particularly
whether you need to retain audit data for activity that was subsequently
lost due to reverting to a backup. And what are you supposed to do if
(read: when) the audit database is trashed or crashes?

[Ots]

On May 3, 1:50 pm, --CELKO-- <[e-mail]:

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

Go tell the lawyers and the accountants that the audit data is being
kept in the same schema as the data being audited. They will want to
put that in the "reason for termination" in your personnel file.

Get a third party audit tool that will run outside the schema and trap
things as they cross the system boundary. This data should then be
sent to physically separate data store, so that if (read: WHEN) the
database is trashed or crashes, you can still reconstruct the audit
trail. Do you keep your backup on the same hard drive as the
database? No! Of course not! This is the same thing.

My goodness! Maybe I should explain a little... this is a
configuration database for a large
industrial process. The database itself is backed up every night, so
the machinery / process can
always be restored to a known working state. The purpose of my audit
table, which is indeed
stored in the same database, is so that when the process begins
behaving differently, folks can
go back and see that, during a previous shift, an operator tweaked (or
fat fingered) an operational
value.

I suppose the audit table could indeed provide a "reason for
termination" someday, but hopefully
not mine...

[Ots]

You were right, Erland, the performance of the trigger was quite poor.
In considering
your response, and after looking through your online documention
(thanks for the
providing such informative work on dynamic SQL, it's much
appreciated), I re-wrote the
trigger for one table using no dynamic SQL. I compare the inserted and
deleted logical
tables field by field, and it seems to perform much better. This
particular table has over
130 columns, however, and hand coding is quite tedious (the test
trigger had over
130,000 characters. So, of course, I'd like to automate the task,
which brings me back
to my original post.

It would seem straightforward enough to build the trigger as a varchar
string, looping through
the table's column names and inserting the 15 or so lines that check
for an insert,
update, or delete for each. Unfortunately, I'm stuck w/ SQL 2000, and
would quickly exceed the
8000 character limit. In the past I used your tip of building the SQL
statement in pieces,
and using EXEC while concatenating the pieces together (thanks
again!), but in that case
I knew how many pieces I would need in advance. Here I do not, as the
size of the entire
'CREATE TRIGGER ...' procedure is dependant upon the number of columns
in the table,
which is variable.

I could go the third-party audit software route, but then I wouldn't
learn anything. I could use
something like VB to create the text of the trigger, and then just cut
and paste it into Query
Analyzer, but my employer wants this done as a stored procedure (they
want to limit the
number of external applications that have to be maintained).

So, what I'd like to do is write a stored procedure that dynamically
builds the individual pieces
of the trigger column by column, then use EXEC( col1trigger +
col2trigger + ... + col_n_trigger)
to create the trigger itself.

The question is how to handle a variable number of arguments to
EXEC... any ideas out
there?

On May 2, 4:29 pm, Erland Sommarskog <[e-mail]:

Ots ([e-mail]) 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, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

> My goodness! Maybe I should explain a little... this is a configuration database for a large industrial process. The database itself is backed up every night, so the machinery / process can always be restored to a known working state. <<

Okay, this is not SOX or one of those things,and you do not care that
you can be 24 hours out of date on the machinery configurations.

But what about loss of the production data. "How many widgets did we
make today?" "I don't know; the DB was blown up by Lichtenstein
terrorists and all we have left is yesterday's stats." Is that on
another machine with a proper back up and audit trail?

>The purpose of my audit table, which is indeed stored in the same database, is so that when the process begins behaving differently, folks can go back and see that, during a previous shift, an operator tweaked (or fat fingered) an operational value. <<

LOL! I have not heard "fat fingers" in years! We also used "OS
problem", but OS was "operator stupidity" and not "operating system".
On a more serious note, have you looked at Sequential Analysis and Ev
Op techniques to go against that data? SAS or SPSS might be able to
give you a lot of good info on the process.

>I suppose the audit table could indeed provide a "reason for termination" someday, but hopefully not mine... <<

LOL!

[schal]

On May 11, 5:17 pm, Ots <[e-mail]:

You were right, Erland, the performance of the trigger was quite poor.
In considering
your response, and after looking through your online documention
(thanks for the
providing such informative work on dynamic SQL, it's much
appreciated), I re-wrote the
trigger for one table using no dynamic SQL. I compare the inserted and
deleted logical
tables field by field, and it seems to perform much better. This
particular table has over
130 columns, however, and hand coding is quite tedious (the test
trigger had over
130,000 characters. So, of course, I'd like to automate the task,
which brings me back
to my original post.

It would seem straightforward enough to build the trigger as a varchar
string, looping through
the table's column names and inserting the 15 or so lines that check
for an insert,
update, or delete for each. Unfortunately, I'm stuck w/ SQL 2000, and
would quickly exceed the
8000 character limit. In the past I used your tip of building the SQL
statement in pieces,
and using EXEC while concatenating the pieces together (thanks
again!), but in that case
I knew how many pieces I would need in advance. Here I do not, as the
size of the entire
'CREATE TRIGGER ...' procedure is dependant upon the number of columns
in the table,
which is variable.

I could go the third-party audit software route, but then I wouldn't
learn anything. I could use
something like VB to create the text of the trigger, and then just cut
and paste it into Query
Analyzer, but my employer wants this done as a stored procedure (they
want to limit the
number of external applications that have to be maintained).

So, what I'd like to do is write a stored procedure that dynamically
builds the individual pieces
of the trigger column by column, then use EXEC( col1trigger +
col2trigger + ... + col_n_trigger)
to create the trigger itself.

The question is how to handle a variable number of arguments to
EXEC... any ideas out
there?

On May 2, 4:29 pm, Erland Sommarskog <[e-mail]:

Ots ([e-mail]) 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, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

- Show quoted text -

Create a temp table with columns TableName, ColumnName
-Accumilate the tablename and columnname in this temp table using a
sysobjects and syscolumns table

select distinct o.name TableName, c.name ColumnName
into #temp
from sysObjects o (nolock)
inner join sysColumns c (nolock) on o.id =c.id
where o.xtype ='U'

now you have a table with a list of table names and column names in a
database
create a cursor and loop thru this table with TableName.ColumnName,
and have all the text for the create trigger in a 2 varchar variables
and do
@var1 + @tablename +'.' + @columnname + @var2

This will create your trigger create statements
either you can print it and run it yourself, this way you can review
what you are executing
or use exec to run all these in the same cursor.

As Erland pointed out dynamic SQL is not good, but as long as this is
a one time script you should be fine ( I am assuming you won't have to
run this script once you have created all the triggers you need)

Word of caution, having this many triggers on a database can have very
very adverse affects..
its easier to maintain a "PreviousWorkingValues" table and have all
the sprocs updating the actual tables update this table too.
Its a lot of work compared to creating triggers using dynamic sql, But
it will be very well worth it.

[Erland Sommarskog]

Ots (ot******@yahoo.com) writes:

It would seem straightforward enough to build the trigger as a varchar
string, looping through the table's column names and inserting the 15 or
so lines that check for an insert, update, or delete for each.
Unfortunately, I'm stuck w/ SQL 2000, and would quickly exceed the 8000
character limit.

I've already said that T-SQL is a very poor tool for the task.

I could use something like VB to create the text of the trigger, and
then just cut and paste it into Query Analyzer, but my employer wants
this done as a stored procedure (they want to limit the number of
external applications that have to be maintained).

We don't want too many tools in this company! You must not use hammers!
We have very good screwdrivers! Use them to get the nails in place!

Doing this in a stored procedure in SQL 2000 is just sheer stupidity.
In SQL 2005, it's different, as you could put your VB code in a CLR
stored procedure. And if you still like to hurt yourself and do string
manipulation in T-SQL, you could use nvarchar(MAX).

I'll tell you what, if your employer is that boneheaded, tell him
that you really must use Screwdriver 2005 for this, but the Express version
is sufficient. That is, download and install an instance of SQL Express
on the same machine, and set up a linked server. Built the triggers on
SQL Express, and then use EXEC() AT to deploy them on SQL 2000.

Of course, you will still do the job faster with a VB or a Perl hammer,
assuming that you have experience of string manipulation in these
languages. Then again, since you are employed and not a consultant that
charge by the other, maybe your employer doesn't care.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ots]

The job has been completed. To follow Erland's analogy, I used "Hammer
2003" (VB.NET) as he is right, there is no sensible way to accomplish
this in a stored procedure. Thanks to the tips on dynamic SQL, the new
and improved triggers run much faster, and can now be generated and
installed w/ the touch of a button.

Thanks again to everyone for their help!

On May 13, 12:59 pm, Erland Sommarskog <[e-mail]:

>
We don't want too many tools in this company! You must not use hammers!
We have very good screwdrivers! Use them to get the nails in place!

Doing this in a stored procedure in SQL 2000 is just sheer stupidity.
In SQL 2005, it's different, as you could put your VB code in a CLR
stored procedure. And if you still like to hurt yourself and do string
manipulation in T-SQL, you could use nvarchar(MAX).

I'll tell you what, if your employer is that boneheaded, tell him
that you really must use Screwdriver 2005 for this, but the Express version
is sufficient. That is, download and install an instance of SQL Express
on the same machine, and set up a linked server. Built the triggers on
SQL Express, and then use EXEC() AT to deploy them on SQL 2000.

Of course, you will still do the job faster with a VB or a Perl hammer,
assuming that you have experience of string manipulation in these
languages. Then again, since you are employed and not a consultant that
charge by the other, maybe your employer doesn't care.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx