|
|
Posted by Ots on 05/11/07 21:17
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...@sommarskog.se> wrote:
> 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...@sommarskog.se
>
> 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
Navigation:
[Reply to this message]
|