Copy row in trigger instead of update?

I think this is a tough one, at least with my limited knowlege. Here
is what I am trying to do.
Background:
I want to create an audit trail for edits made to a database. I want
to do this by catching the update in a SQL trigger and turning it into
an insert with a time stamp and cancel the update.
So any row you change really does not change, it is copied to a new
row in the same table with a time stamp.
Copying the row is normally easy as in:
Insert into mytable select * from mytable where id= '17'
This will not work though, The problem with this is that id is an
Identity field and you cannot specify the value of an identity. So the
first trick is to basically do a select * (except id). I figured this
out here is the code:
_________________________________________________________________
declare @.tb sysname, @.col sysname, @.identid sysname
select @.tb='testupdate',
@.col='id'
declare @.sql varchar(2000)
select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
from information_schema.columns
where column_name!=@.col
and table_name=@.tb
select @.sql='insert into ' + @.tb + ' ' +
substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where
id=17'
print @.sql
exec(@.sql)
The problem with this is that it copies every row in the table. I
need to copy just the changed row. The above code does not include the
timestamp part here is that part, and separate section of code that
will be in the same trigger.
UPDATE testupdate
SET testupdate.updatedate = GETDATE()
FROM testupdate INNER JOIN Inserted ON testupdate.id = Inserted.id
This little bit of code effectively creates a time stamp on every
edited row and obviously get the id of the changed row. Now if I could
capture that id and use it in the first bit of code I think I would
have it.
I tried to do something like this
CREATE TRIGGER testtrig
ON testupdate
FOR UPDATE
AS
declare @.tb sysname, @.col sysname, @.myid sysname
select @.tb='testupdate',
@.col='id',
@.myid = @.@.identity
declare @.sql varchar(2000)
select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
from information_schema.columns
where column_name!=@.col
and table_name=@.tb
select @.sql='insert into ' + @.tb + ' ' +
substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where id='
+ @.myid
print @.sql
exec(@.sql)
UPDATE testupdate
SET testupdate.updatedate = GETDATE()
FROM testupdate INNER JOIN Inserted ON testupdate.id = Inserted.idThere are several things you might consider.
If you can, add a column to the table with a default value of getdate, so
all inserts are covered.
Then read about instead of triggers... They are new to SQL 2000, the normal
trigger fires AFTER the action, which is causing you a problem. An instead
of trigger fires INSTEAD OF the update ( for instance). You still get the
inserted ,deleted tables. The ACTUAL update only occurs IF you include an
update in your trigger... you could write something like
create trigger mytrig on mytable instead of update
as
insert into mytable select * from inserted
The original update would NOT occur, and instead you would have inserted the
NEW value of the row. The NEW row would have a new ID of course... you'd
have to mess with that...perhaps have a second integer column which
contains the original ID..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"OrlandoRocks" <parkJunkie@.orlandorocks.com> wrote in message
news:55879c92.0409120419.8418a63@.posting.google.com...
> I think this is a tough one, at least with my limited knowlege. Here
> is what I am trying to do.
> Background:
> I want to create an audit trail for edits made to a database. I want
> to do this by catching the update in a SQL trigger and turning it into
> an insert with a time stamp and cancel the update.
> So any row you change really does not change, it is copied to a new
> row in the same table with a time stamp.
> Copying the row is normally easy as in:
> Insert into mytable select * from mytable where id= '17'
> This will not work though, The problem with this is that id is an
> Identity field and you cannot specify the value of an identity. So the
> first trick is to basically do a select * (except id). I figured this
> out here is the code:
> _________________________________________________________________
> declare @.tb sysname, @.col sysname, @.identid sysname
> select @.tb='testupdate',
> @.col='id'
> declare @.sql varchar(2000)
> select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
> from information_schema.columns
> where column_name!=@.col
> and table_name=@.tb
> select @.sql='insert into ' + @.tb + ' ' +
> substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where
> id=17'
> print @.sql
> exec(@.sql)
>
> The problem with this is that it copies every row in the table. I
> need to copy just the changed row. The above code does not include the
> timestamp part here is that part, and separate section of code that
> will be in the same trigger.
> UPDATE testupdate
> SET testupdate.updatedate = GETDATE()
> FROM testupdate INNER JOIN Inserted ON testupdate.id => Inserted.id
>
> This little bit of code effectively creates a time stamp on every
> edited row and obviously get the id of the changed row. Now if I could
> capture that id and use it in the first bit of code I think I would
> have it.
> I tried to do something like this
>
> CREATE TRIGGER testtrig
> ON testupdate
> FOR UPDATE
> AS
> declare @.tb sysname, @.col sysname, @.myid sysname
> select @.tb='testupdate',
> @.col='id',
> @.myid = @.@.identity
> declare @.sql varchar(2000)
> select @.sql=isnull(@.sql,'select ')+quotename(column_name)+','
> from information_schema.columns
> where column_name!=@.col
> and table_name=@.tb
> select @.sql='insert into ' + @.tb + ' ' +
> substring(@.sql,1,len(@.sql)-1)+ ' from '+quotename(@.tb) + ' where id='
> + @.myid
> print @.sql
> exec(@.sql)
> UPDATE testupdate
> SET testupdate.updatedate = GETDATE()
> FROM testupdate INNER JOIN Inserted ON testupdate.id => Inserted.id