|
|
| Article |
Kishore
Contributing Member
Articles: 10 Comments: 3
|
|
|
| Posted: Tue Jul 08, 2008 12:07 pm |
|
|
SQL server has a nice OUTPUT clause which can be used for retrieving the deleted/updated/inserted information from Data Manipulation Language (DML) commands. This article shows few examples on how to fetch the affected rows after executing INSERT,UPDATE or DELETE commands.
First of all create a temporary table variable for holding the affected rows information.
| Code: | | Declare @tempAffectedData table (Sno,Name); |
Sno and Name are the two columns which should be same as the test tables in examples below. You should change the columns when you try with your queries.
1. INSERTED Rows
When inserting into a table we can find newly inserted rows using INSERTED variable.
Example
| Code: | INSERT TestTable
OUTPUT INSERTED.Sno, INSERTED.Name
INTO @tempAffectedData
VALUES (1, 'Alexander');
-- inserted data
select * from @tempAffectedData |
2. DELETED
When deleting the records from a table OUTPUT clause can help to find the deleted rows
| Code: |
DELETE TestTable
OUTPUT DELETED.* INTO @tempAffectedData;
-- deleted data
select * from @tempAffectedData
|
3. DELETED and INSERTED for finding updated
Now we can use both INSERTED and DELETED variables to find the updated rows in a table.
| Code: |
-- new table variable to catch the affected rows
Declare @tempaffectedRows table (Name,deletedname);
UPDATE TestTable
SET NAME = 'Alexander Senr'
OUTPUT INSERTED.Name,
DELETED.Name
INTO @tempaffectedRows
WHERE Sno=1;
-- updated rows
select * from @tempaffectedRows
|
OUTPUT clause can be really handy when you want to know details about the affected rows after a DML command.
Post your comments on this article.
Thanks |
|
|
| Comments |
aaaa78101
Author
Joined: 01 Jun 2011
Articles: 0
Comments: 3
|
| Posted: Wed Jun 01, 2011 7:45 am Post subject: |
|
|
If you want your trigger to report all affected rows, you need to write a loop
USE FyiCenterData;
GO
select * from fyi_users;
-- reporting the first affected row only
UPDATE fyi_users SET email=name;
GO
Email changed from NULL to John King
(5 row(s) affected)
-- reporting all affected rows
ALTER TRIGGER update_user ON fyi_users
AFTER UPDATE
AS
SELECT 'Email changed from '
+ ISNULL(d.email,'NULL')
+ ' to '
+ ISNULL(i.email,'NULL')
FROM INSERTED AS i, DELETED AS d
WHERE i.id = d.id;
GO
UPDATE fyi_users SET email=REVERSE(name);
GO
------------------------------------------------------
Email changed from Marc Kumar to ramuK craM
Email changed from Roy Bush to hsuB yoR
Email changed from Jack Gate to etaG kcaJ
Email changed from Nancy Greenberg to grebneerG ycnaN
Email changed from John King to gniK nhoJ
(5 row(s) affected)
(5 row(s) affected)
oracle Application development in ahmedabad
PHP development in ahmedabad
wordpress Development in ahmedabad
Oracle company in ahmedabad |
|
|
| |
Page 1 of 1 |
All times are GMT
You cannot post articles in this chapter
You cannot edit your articles in this chapter
You cannot delete your articles in this chapter
You cannot rate articles in this chapter
You cannot post comments in this chapter
You cannot edit your comments in this chapter
You cannot delete your comments in this chapter
You cannot rate comments in this chapter
|
|
