or “Who needs a trigger anyway?”
The output clause was, I think, one of those wonderful features of SQL 2005 that very few people used, myself included. Now in 2008, it’s even better, but still doesn’t appear to be widely used.
The output clause can be used to get, as a resultset, data from the inserted and deleted tables that are usually only visible in a trigger. As a very simple example:
Create Table #Testing (
id int identity,
somedate datetime default getdate()
)
insert into #Testing
output inserted.*
default values
Neat. We can get back the inserted values as a result set. We can also insert them into a table variable for later processing. Using the same temp table
declare @OutputTable TABLE (id int, somedate datetime)
insert into #Testing
output inserted.* into @OutputTable
default values
select * from @OutputTable
Very neat. Now how about a practical example? Say we have the following three tables in a database.
Create Table ParentTable (
ID int identity primary key,
ParentDescription varchar(50),
CreationDate DATETIME DEFAULT GETDATE()
)
GO
CREATE TABLE ChildTable (
ID Int identity Primary Key,
ParentID int not null constraint fk_parent foreign key references ParentTable(ID),
Somedescription varchar(20),
SomeValue Money
)
GO
Create Table AuditTable (
AuditID int identity primary key,
ChildID int,
SomeValue Money,
InsertDate DATETIME DEFAULT GETDATE(),
OriginatingLogin VARCHAR(50) DEFAULT ORIGINAL_LOGIN()
)
We get a set of data (perhaps in a temp table, perhaps in an xml document) that needs to be inserted into those tables. The source data will have multiple parent rows, each with multiple child rows. Those need to be inserted into the appropriate tables and the foreign keys have to be assigned correctly. In addition, the ID of the child rows, along with the value and the current date must be written into an audit table, along with the login name of the current user.
(more…)