Changed fields

In our applications we have a table named ChgMstr (Change
Log) that logs changes made to each record. It includes
the Field Name, Date & Time, the user making the change,
and the Before and After values of the field.
I am trying to setup an Update trigger that will loop
through the Deleted and Inserted fields, find which fields
have changed, and then either INSERT directly into the
ChgMstr table or call a Stored Procedure to do it.
However, I cannot find a way (even with Dynamic SQL) to
build a SELECT string that can be EXECuted. When I do, I
get "Invalid Object" messages for both Deleted and
Inserted. It appears that once the EXEC statement is sent
that Deleted and Inserted go out of scope.
Anyone got any ideas. I can provide the contents of the
trigger, if needed.Bryan,
The inserted and deleted tables will not be available to a stored
procedure. You could copy their contents into a temporary table for use by
the stored procedure, but it seems to me unlikely that you need a stored
procedure just to insert into your ChgMstr table. Can you do something like
this:
insert into ChgMster
select N'ColumnA', getdate(), suser_sname(), d.ColumnA, i.ColumnA
from inserted i, deleted d
where i.primaryKey = d.primaryKey
union all
select N'ColumnB', getdate(), suser_sname(), d.ColumnB, i.ColumnB
from inserted i, deleted d
where i.primaryKey = d.primaryKey
This assumes that the primary key column is not modified. If it is, unless
you have another unique constraint on columns that don't change, the
question of what the original and final values of that column are is not
discernable from the inserted and deleted tables in some multi-row updates,
such as
update T set
pk = case when 1 then 11 when 4 then 12 when 8 then 13 end
from T
where pk in (1, 4, 8)
Then the inserted table will contain rows with pk 11, 12, and 13, and the
deleted table will contain rows with pk 1, 4, and 8, but there is no way to
say which of 1, 4, and 8 because which of 11, 12, 13 by looking at the
inserted and deleted tables.
SK
"Bryan A. Jackson" <bjackson@.focuscmc.com> wrote in message
news:ae5701c40cee$28616dc0$a001280a@.phx.gbl...
> In our applications we have a table named ChgMstr (Change
> Log) that logs changes made to each record. It includes
> the Field Name, Date & Time, the user making the change,
> and the Before and After values of the field.
> I am trying to setup an Update trigger that will loop
> through the Deleted and Inserted fields, find which fields
> have changed, and then either INSERT directly into the
> ChgMstr table or call a Stored Procedure to do it.
> However, I cannot find a way (even with Dynamic SQL) to
> build a SELECT string that can be EXECuted. When I do, I
> get "Invalid Object" messages for both Deleted and
> Inserted. It appears that once the EXEC statement is sent
> that Deleted and Inserted go out of scope.
> Anyone got any ideas. I can provide the contents of the
> trigger, if needed.|||1. Forgive my ignorance, but what does the 'N' in select
N'Column A' do?
2. Will I have to do a union for each field in the table?
I'm looking for a generic way to do this so the trigger
does not need to be changed each time fields are added or
deleted.
Thanks,
Bryan

>--Original Message--
>Bryan,
> The inserted and deleted tables will not be available
to a stored
>procedure. You could copy their contents into a
temporary table for use by
>the stored procedure, but it seems to me unlikely that
you need a stored
>procedure just to insert into your ChgMstr table. Can
you do something like
>this:
>insert into ChgMster
>select N'ColumnA', getdate(), suser_sname(), d.ColumnA,
i.ColumnA
>from inserted i, deleted d
>where i.primaryKey = d.primaryKey
>union all
>select N'ColumnB', getdate(), suser_sname(), d.ColumnB,
i.ColumnB
>from inserted i, deleted d
>where i.primaryKey = d.primaryKey
>This assumes that the primary key column is not
modified. If it is, unless
>you have another unique constraint on columns that don't
change, the
>question of what the original and final values of that
column are is not
>discernable from the inserted and deleted tables in some
multi-row updates,
>such as
>update T set
> pk = case when 1 then 11 when 4 then 12 when 8 then 13
end
>from T
>where pk in (1, 4, 8)
>Then the inserted table will contain rows with pk 11, 12,
and 13, and the
>deleted table will contain rows with pk 1, 4, and 8, but
there is no way to
>say which of 1, 4, and 8 because which of 11, 12, 13 by
looking at the
>inserted and deleted tables.
>SK
>"Bryan A. Jackson" <bjackson@.focuscmc.com> wrote in
message
>news:ae5701c40cee$28616dc0$a001280a@.phx.gbl...
(Change
fields
I
sent
>
>.
>|||Bryan,
The N just signifies that the string is Unicode. Object names in SQL
Server are in Unicode, but it won't hurt to leave this out if the name
only uses ASCII.
I generally don't think it's a great idea to write generic code that
doesn't "know" the table structure. You could generate the code for
triggers like this automatically with something like this:
select 'insert into ChgMster' oneLine, 0 ORDINAL_POSITION
union all
select 'select
'+q_column_name+',
'+' getdate(),
suser_sname(),
d.'+q_column_name+',
'+' i.'+q_column_name+'
from inserted i, deleted d
where i.primaryKey = d.primaryKey
and i.'+q_column_name+'<>d.'+q_column_name+
case when ORDINAL_POSITION = (
select max(ORDINAL_POSITION)
from Northwind.INFORMATION_SCHEMA.Columns
where TABLE_NAME = 'Orders'
) then '' else '
union all'
end, ORDINAL_POSITION
from (
select quotename(COLUMN_NAME) as q_column_name, ORDINAL_POSITION
FROM Northwind.INFORMATION_SCHEMA.Columns
where TABLE_NAME = 'Orders'
) C
order by ORDINAL_POSITION
There are also third-party products to do this kind of logging.
SK
Bryan A. Jackson wrote:
>1. Forgive my ignorance, but what does the 'N' in select
>N'Column A' do?
>2. Will I have to do a union for each field in the table?
>I'm looking for a generic way to do this so the trigger
>does not need to be changed each time fields are added or
>deleted.
>Thanks,
>Bryan
>
>
>to a stored
>
>temporary table for use by
>
>you need a stored
>
>you do something like
>
>i.ColumnA
>
>i.ColumnB
>
>modified. If it is, unless
>
>change, the
>
>column are is not
>
>multi-row updates,
>
>end
>
>and 13, and the
>
>there is no way to
>
>looking at the
>
>message
>
>(Change
>
>fields
>
>I
>
>sent
>