Is it possible in an SQL Update Trigger to get values that where for
the record before the update statement was run and then insert these
pre-update values and new values in to a different table?

Two tables exists while executing the trigger

DELETED (containing the structure and the old values)
INSERETD (containing the structure and the new values)

HTH, Jens Suessmeyer.

In triggers, within the transaction you have the inserted and deleted table
availabe.
inserted contains the new values, deleted the old

Here is an example


if object_id ('tbl_trigger') is not null
drop table tbl_trigger

create table tbl_trigger (colid int IDENTITY (1,1) PRIMARY KEY, colvalue
int)
go
create trigger trg_on_update_tbl_triggger on tbl_trigger
for update
as
   begin
     select i.colid,o.colvalue as oldvalue, i.colvalue as newvalue from
deleted o
        join inserted i
        on o.colid = i.colid
   end

insert into tbl_trigger (colvalue) VALUES (5)

update tbl_trigger
set colvalue = colvalue * 2
where colId = 1

Show quoteHide quote

Yes...unless you are using only a natural key.  If a column in the natural
key is updated and multiple rows are affected by the same update statement,
then it is not possible to determine with certainty what the old and new
values are for each row.  SQL Server doesn't have a FOR EACH ROW trigger
like Oracle. This is a serious limitation and one of the reasons I always
use surrogate keys.  This limitation exists in SQL Server 2005 too.  I've
already expressed my concerns to Microsoft via sqlwish.

If you don't use surrogate keys, then you must (1) only issue single row
updates, or (2) add an IDENTITY or ROWGUID column.

I discovered a workaround, but it depends on implementation-specific
behavior, and was cautioned against using it by Microsoft engineers.



<robin9***@hotmail.com> wrote in message
Show quoteHide quote

Find duplicates
How to: CrossTab or Pivot
Need to convert Date from "YYYY-MM-DD 00:00:00.0000" to "MM/DD/YYYY"
select in range
When was a database accessed last