|
|
Posted by schal on 05/13/07 16:21
On May 11, 5:17 pm, Ots <otsmc...@yahoo.com> wrote:
> 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- 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.
Navigation:
[Reply to this message]
|