Re: audit trigger

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Darren Woodbrey (darrenwoodbrey_at_hpfairfield.com)
Date: 06/07/04 

Date: Mon, 7 Jun 2004 16:37:02 -0400

What do you mean by compound key? I am getting a primary key error. How do
I initialize @PKCols? I am new to SQL and have found this code from the
web. Sorry for the lack of knowledge. Thanks!

"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:OLLZdsMTEHA.3844@TK2MSFTNGP11.phx.gbl...
> Hi
>
> Your procedure will not work when you get a compound key. I am not sure
why
> you have the temporary tables direct use of the inserted/deleted "tables"
> should be ok! There is no DDL for the audit table and even if it did work
> you may get primary key problems. As @PKCols is not initialised it will
> always be NULL. The method you use to create the value of @PKCols is not
> regarded as safe and the best way is usually a cursor. There is alot of
work
> in this trigger which could be reduced and speeded up by having table
> specific code even through maintainance will be a nightmare. You may also
> find that the audit table is a bottleneck.
>
> John
>
>
> "Darren Woodbrey" <darrenwoodbrey@hpfairfield.com> wrote in message
> news:O2ZQJYMTEHA.3988@tk2msftngp13.phx.gbl...
> > I am trying to implement an audit trigger I found on the net. It gives
me
> > an error when I try to update a record saying "no PK on table wo_main".
> The
> > table wo_main does have a primary key 'entry'. Can anyone help me out
> with
> > this? Here is the trigger:
> >
> > create trigger tr_wo_main on wo_main for update
> > as
> > declare @bit int ,
> > @field int ,
> > @maxfield int ,
> > @char int ,
> > @fieldname varchar(128) ,
> > @TableName varchar(128) ,
> > @PKCols varchar(1000) ,
> > @sql varchar(2000),
> > @UpdateDate varchar(21) ,
> > @UserName varchar(128)
> >
> > select @TableName = 'wo_main'
> > -- date and user
> > select @UserName = system_user ,
> > @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' +
> > convert(varchar(12), getdate(), 114)
> > -- get list of columns
> > select * into #ins from inserted
> > select * into #del from deleted
> >
> > -- Get primary key columns for full outer join
> > select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' +
> c.COLUMN_NAME
> > + ' = d.' + c.COLUMN_NAME
> > from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
> > where pk.TABLE_NAME = @TableName
> > and CONSTRAINT_TYPE = 'PRIMARY KEY'
> > and c.TABLE_NAME = pk.TABLE_NAME
> > and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
> >
> > if @PKCols is null
> > begin
> > raiserror('no PK on table %s', 16, -1, @TableName)
> > return
> > end
> >
> > select @field = 0, @maxfield = max(ORDINAL_POSITION) from
> > INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
> > while @field < @maxfield
> > begin
> > select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> > where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
> > select @bit = (@field - 1 )% 8 + 1
> > select @bit = power(2,@bit - 1)
> > select @char = ((@field - 1) / 8) + 1
> > if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
> > begin
> > select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> > TABLE_NAME = @TableName and ORDINAL_POSITION = @field
> > select @sql = 'insert Audit (TableName, FieldName, OldValue,
> NewValue,
> > UpdateDate, UserName)'
> > select @sql = @sql + ' select ''' + @TableName + ''''
> > select @sql = @sql + ',''' + @fieldname + ''''
> > select @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
> > select @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
> > select @sql = @sql + ',''' + @UpdateDate + ''''
> > select @sql = @sql + ',''' + @UserName + ''''
> > select @sql = @sql + ' from #ins i full outer join #del d'
> > select @sql = @sql + @PKCols
> > select @sql = @sql + ' where i.' + @fieldname + ' <> d.' +
@fieldname
> > select @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' +
> > @fieldname + ' is not null)'
> > select @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.'
+
> > @fieldname + ' is null)'
> >
> > exec (@sql)
> > end
> > end
> > go
> >
> >
> > Thanks in advance for any help you can give.
> > Darren
> > MCP
> >
> >
>
>

Relevant Pages

  • Re: audit trigger
    ... But as I stated the select statement that populates @PKCols could cause ... I am getting a primary key error. ... >> Your procedure will not work when you get a compound key. ... There is no DDL for the audit table and even if it did ...
    (microsoft.public.sqlserver.programming)
  • Re: Inserting a new PK into an existing table
    ... could be desirable to have an additional primary key column because: ... The Only benefit fo using the pseudoKey is the space savings, ... number of columns in the compound key is many or the total space is ... I also hate rownum, but this is a good example of some usefulness from ...
    (comp.databases.oracle.misc)
  • Re: Using seek to find if a record exists
    ... a compound primary key cannot have Nulls in any of the fields, ... >>so your candidate compound key would be an extremely poor choice ... > poor decision if the birthdate were required for all records. ... where the compound key you describe could possibly have been used. ...
    (comp.databases.ms-access)
  • Re: Trend towards artificial keys (GUIDs) sez my textbook...is AI next?
    ... Sometimes they conflict and one has to make tradeoffs. ... Sometimes no such ideal key exists. ... primary key that includes a PRIMARY key of the first table. ... It is fairly common for something like an order line item to have a compound key. ...
    (comp.databases.theory)