In MSSQL 2005 is new T-SQL command OUTPUT, that was used only as output parameters in previous MSSQL version. Now you can use it in T-SQL commands to make data changes and see results by one command without using Triggers.
When you inserting new row, OUTPUT can returns you inserted record includes identity ID columns. For get ID value you must used Scope_Identity(), or get last value after insert. This is very easy now.
Example:
INSERT INTO Companies (CompanyName, WebSite)
OUTPUT inserted.ID, inserted.CompanyName
VALUES ('Select SQL', 'www.select-sql.com')
This will return you:
ID CompanyName ----------- ---------------------- 3 Select SQL
(1 row(s) affected)
In update command you can use inserted or deleted. You can use both in one query. It shows you data after update (inserted table) and before update (deleted table).
Example:
UPDATE Companies SET CompanyName = 'New name' OUTPUT inserted.CompanyName, deleted.CompanyName, inserted.ID WHERE ID = 3
Query return:
NewCompanyName OldCompanyName ID ------------------ -------------------- ----------- New name Select SQL 3
(1 row(s) affected)
Same way you can use OUTPUT in DELETE command, to show deleted record.
Example:
DELETE FROM Companies OUTPUT deleted.CompanyName, deleted.ID WHERE ID = 2
Returns:
CompanyName ID ------------------------------- ----------- Select SQL 2
(1 row(s) affected)
