473,735 Members | 6,374 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.

May 2 '07 #1
9 9310
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****@sommarsk og.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
May 2 '07 #2
>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.

May 3 '07 #3
--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?
May 3 '07 #4
Ots
On May 3, 1:50 pm, --CELKO-- <jcelko...@eart hlink.netwrote:
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...

May 11 '07 #5
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 <esq...@sommars kog.sewrote:
Ots (otsmc...@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, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

May 11 '07 #6
> 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!

May 12 '07 #7
On May 11, 5:17 pm, Ots <otsmc...@yahoo .comwrote:
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 <esq...@sommars kog.sewrote:
Ots (otsmc...@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, esq...@sommarsk og.se
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/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.Colum nName,
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 "PreviousWorkin gValues" 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.

May 13 '07 #8
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****@sommarsk og.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
May 13 '07 #9
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 <esq...@sommars kog.sewrote:
>
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, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

May 15 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4197
by: Trevor Fairchild | last post by:
I am trying to create a very minimal auditing system for a series of databases. I am in the process of writing Update triggers for 5 Tablse. I will write a trigger for each table-the trigger's function will be to INSERT a row into my MasterChanges table everytime ANY data is changed in each of the 5 tables. I have set up MasterChanges to capture the following: what Table the change was made in
4
6299
by: Steve Bishop | last post by:
I have an Insert, Update and delete trigger on a table that works fine. It records the changes into another table called tblTracking. tblTracking records the changes so they can be uploaded to another server off site. The problem is that it fires each time my DTS package fires (every 15 minutes). The DTS package imports data from an ODBC database then compares what is in the source/destination table with a Insert Into, select not in (for...
9
3459
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM, VARCHAR(10) WO.PROBLEMCODE, VARCHAR(8)
10
2052
by: heromull | last post by:
We have an asp.net app with about 200 data entry forms. Customers may enter data into any number of forms. Each form's data is persisted in a corresponding sql table. When data entry is complete, it needs to be processed. Here's where the questions start. How can we easily determine in which tables a customer has data and how best to select that data? We're not opposed to putting all the data in a single table. This table would...
6
7143
by: Mary | last post by:
We are developing a DB2 V7 z/OS application which uses a "trigger" table containing numerous triggers - each of which is activated by an UPDATE to a different column of this "trigger" table. When the triggers are fired, various other operations are performed on other tables in the database. The triggers are not created on these other tables because other programs perform updates to these tables and we do not want the triggers to fire...
5
2701
by: Raphael Bauduin | last post by:
Hi, I'm looking at the logging of a database we'll put in production soon. I've seen some posts on this list about history tables, like mentioned in http://www-106.ibm.com/developerworks/web/library/wa-dbdsgn2.html . I think I'll go that way too, but I still have some questions on the approach, and would appreciate any advice on it. Here are some questions I have: - is it possible to write only one function used for all logging...
3
4903
by: aniphilip | last post by:
Hi All, I have a requirement to automate Access functionality from Excel. I need to open Access, trigger button events and close Access from Excel. I Achieved opening Access from Excel. I cannot find a way to trigger a button click event from Excel for an Access form. To be more precise, I have Access db called a.mdb. a.mdb has a.form and a.command button inside the form I have xls sheet called b.xls. How I will fire an onclick event...
4
6987
by: Jim Devenish | last post by:
We have an Access database with Access front-end to Access back-end. Another company has installed a separate application using SQLServer. At present certain information in the first application is entered manually into the second. We wish to automate this process. The other company is proposing that the SQLServer application sets up a trigger on a selected table in the Access backend. When a new record is added, information can be...
0
2977
by: r0cboff | last post by:
Hi there, This is my first time posting in here and I'm hoping somebody can point out where I am going wrong? I am currently trying to use Bitemporal tables. By this I mean a table with a valid times and transaction times. These topics are covered by Joe Celko and Richard Snodgrass in their respective books. I have developed a simple schema to test the relevant constraints which are required to keep all the valid times and transaction...
0
8962
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9463
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9327
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
6049
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4559
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4822
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3273
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2739
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2188
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.