October 10, 2012 at 8:59 am
Hi,
Is there a way of getting the inserted and deleted "virtual tables" using the output on the same statement?
With a trigger we can access the "virtual tables" individually at any time within the trigger but the OUTPUT statement is only available "once" when executing the operation.
For example, if I want to update the salary column on a employees table to store the old salary and new salary and do something with it.
With a trigger I could have a table variable with (empId, salary, operation) and INSERT INTO @tbl (empId, salary, operation) SELECT empid, salary, 'I' FROM inserted UNION SELECT empid, salary, 'D' FROM deleted.
After that I could process all the 'I' and 'D' data...
With an OUTPUT the only way I can think of doing this is to have a table variable with (empid, newSalary, oldSalary) and do UPDATE employees SET ... OUTPUT inserted.empid, inserted.salary newSalary, deleted.salary oldSalary INTO @tbl WHERE ...
Is there anyway of having OUTPUT ... UNION OUTPUT ... ?!
Probably having the two salary columns is better since there are less rows on the table but can it be done like the trigger, access the inserted and deleted in separate?
oh... just one more thing... How does OUTPUT behave with MERGE statement? In a MERGE we can have INSERT/UPDATE/DELETE in one single statement.
Thanks,
Pedro
October 10, 2012 at 9:08 am
In a merge statement it generates an $action which is either Insert, Delete, Update as well as being able to get the Inserted, Deleted data.
Eg
MERGE tblCustomer AS target
USING (SELECT * FROM tblUpdates ) AS source
ON (target.cid = source.cid)
WHEN MATCHED
AND (target.name!=source.name
or target.country!=source.country)
THEN UPDATE SET name = source.name, country = source.country
WHEN NOT matched
THEN INSERT
VALUES (CID,Name, Country)
OUTPUT $action
, getdate()
, CASE WHEN $action = 'INSERT' THEN inserted.name ELSE deleted.name END as name
, CASE WHEN $action = 'INSERT' THEN inserted.country ELSE deleted.country END as country
, binary_checksum(inserted.name, inserted.country) as chkNew
,binary_checksum(deleted.name, deleted.country) as ChkOld
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 10, 2012 at 11:57 am
You can direct the OUTPUT into a table or table variable for subsequent processing.
From BOL:
USE AdventureWorks2012;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
OUTPUT inserted.BusinessEntityID,
deleted.VacationHours,
inserted.VacationHours,
inserted.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 10, 2012 at 1:56 pm
PiMané (10/10/2012)
With an OUTPUT the only way I can think of doing this is to have a table variable with (empid, newSalary, oldSalary) and do UPDATE employees SET ... OUTPUT inserted.empid, inserted.salary newSalary, deleted.salary oldSalary INTO @tbl WHERE ...Is there anyway of having OUTPUT ... UNION OUTPUT ... ?!
That's how I'm making also...
It's probably the best way also since in a trigger we have to join inserted and deleted to access both values..
Thanks,
Pedro
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply