Re: How to access field contents generically in trigger

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Bernie Beattie (BernieBeattie_at_discussions.microsoft.com)
Date: 06/14/04 

Date: Mon, 14 Jun 2004 06:47:01 -0700

I've fixed the datatype 1 character problem.
I think I've fixed the SET @FieldContents line, it now reads :
         SET @cCMD='SET @FieldContents=(SELECT '+@ColName+' FROM DELETED)'
I'm not sure about the scope problem you mentioned, but as I'm still not seeing the field contents, I guess that must be what's wrong now. Can I specify an OUTPUT parameter in this statement or is that only for stored procedures?

Thanks for any help.

Bernie

"Hugo Kornelis" wrote:

> On Mon, 14 Jun 2004 04:46:01 -0700, Bernie Beattie wrote:
>
> >I am trying to create Insert/Update/Delete triggers on my tables but I want a generic trigger that will work for all tables. Basically I want to write a string back to an audit table which will contain the updated field name + the old and new field value.
>
> Hi Bernie,
>
> Please don't. You can't have one trigger for all tables. You can have
> several triggers that are almost copies of each other, but that's really
> shooting yourself (or the person that comes after you) in the foot.
>
> Please please please use seperate triggers for each table. I usually use
> seperate audit tables for each table, with the same structure plus some
> extra columns. In the trigger, I copy all old and new data in the audit
> table. As fast as it gets, plus it's very easay to use SQL to search
> specific events in the audit table (try that with your unnormalized audit
> table!!)
>
> In case you decide to disregard my advise and carry on as you're doing
> now, read on.
>
> (snip your code)
> >The field UPDATES contains all my field names but not their values.
>
> Figures. You put only the first character of the datatype in @ColDataType
> and you then go on to compare that to 'char', 'int', etc. You also forgot
> to include some datatypes (varchar, float, bit, binary - and that is
> without even checking the full list in BOL).
>
> After you fix this, you'll find that your dynamic SQL (yugghh) throws an
> error. That's because "SET @FieldContents=Inserted.Column1" is invalid SQL
> syntax. And when you've fixed that, you'll find that @FieldContents is not
> in scope for the dynamic SQL.
>
> All this can be fixed. Then, when you test it, you'll find that only new
> values are audited, not old+new value (as you require). So you'll have to
> change the code even more. And then, just when you think that your trigger
> works perfectly, someone throws an INSERT, UPDATE or DELETE statements
> that affects more than one row at it - and it's back to the drawingboard
> for you.
>
>
> >Any ideas anyone?
>
> See first paragraphs.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Relevant Pages

  • Re: trigger counter
    ... DATETIME, store GETDATEin it, and use that "identifier"? ... > I am trying to put an audit system on a table to be used as a change order ... > The trigger runs on an update and checks each field to see whether or not ... > an audit table. ...
    (microsoft.public.sqlserver.programming)
  • Re: Possible as ANSI SQL?
    ... In order for the application to write to the audit ... trail, it has to disable that protection trigger. ... >> interacts with SQL server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Strange Trigger Behavior
    ... Since you need write to the log only of there is a change in the standard ... Then, the trigger will not insert a record, if there is ... > to alert us if a change was made to a standard cost. ... > A coworker of mine pointed out that about 200,000 rows hit the audit file. ...
    (microsoft.public.sqlserver.programming)
  • Re: audit trigger
    ... There is no DDL for the audit table and even if it did work ... The method you use to create the value of @PKCols is not ... > I am trying to implement an audit trigger I found on the net. ... > table wo_main does have a primary key 'entry'. ...
    (microsoft.public.sqlserver.programming)