SQL Server 2005 introduces a new TSQL feature that allows you to retrieve data affected by insert/update/delete statements easily. This is achieved by the use of OUTPUT clause which can reference columns from the inserted and deleted tables (that are available from triggers currently) or expressions. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table.
Let us take a look at a common scenario now and how OUTPUT clause can be used to solve the problem. Use of identity column as primary key in a table is a fairly common practice. Additionally, if you have references to the identity column you need to know the value explicitly before inserting the related row. This scenario gets complicated if multiple identity values are generated as a result of inserting multiple rows. In this case, there is no easy way to determine the values that were inserted without using a trigger to populate a temporary table with the generated identity values from the inserted table for example.
The example below shows code that uses OUTPUT clause in UPDATE and DELETE statements to insert rows into an audit table.
-- Create Two tables
create table t ( i int not null );
create table t_audit ( old_i int not null, new_i int null );
-- Insert values into that
insert into t (i) values( 1 );
insert into t (i) values( 2 );
-- Now update values
update t set i = i + 1
output deleted.i, inserted.i
into t_audit where i = 1;
-- Delete some values
delete from t
output deleted.i, NULL
into t_audit where i = 2;
-- Select values to see the result
select * from t;
select * from t_audit;
-- Drop temporary tables
drop table t, t_audit;go
Let us take a look at a common scenario now and how OUTPUT clause can be used to solve the problem. Use of identity column as primary key in a table is a fairly common practice. Additionally, if you have references to the identity column you need to know the value explicitly before inserting the related row. This scenario gets complicated if multiple identity values are generated as a result of inserting multiple rows. In this case, there is no easy way to determine the values that were inserted without using a trigger to populate a temporary table with the generated identity values from the inserted table for example.
The example below shows code that uses OUTPUT clause in UPDATE and DELETE statements to insert rows into an audit table.
-- Create Two tables
create table t ( i int not null );
create table t_audit ( old_i int not null, new_i int null );
-- Insert values into that
insert into t (i) values( 1 );
insert into t (i) values( 2 );
-- Now update values
update t set i = i + 1
output deleted.i, inserted.i
into t_audit where i = 1;
-- Delete some values
delete from t
output deleted.i, NULL
into t_audit where i = 2;
-- Select values to see the result
select * from t;
select * from t_audit;
-- Drop temporary tables
drop table t, t_audit;go
Comments