The modified data is returned in SQL Server

In the company saw colleagues wrote a SQL2005 new features of the article, feel very practical, here and everyone to share under.

This technology is mainly used in the inserted and deleted virtual tables, these two tables believe that everyone is familiar. We used to use it primarily in triggers.

Let's review these two tables first:

The inserted and deleted tables are logical tables, and the two tables are managed by the system, stored in memory, not stored in the database, and therefore not allowed to be modified directly by the user.
The structure of the two tables is in the same table structure as the tables that are acting on the trigger. These two tables are dynamically resident in memory, and when the triggers work, they are also deleted.

Next, take a look at our case, actually the use is very simple, but also very practical. He used the output parameter in addition to the two tables above.

To create a table:

IF  EXISTS(SELECT *  fromSys.objectsWHERE object_id = object_id(N'[TESTTB]') andTypeinch(N'U'))DROP TABLETESTTBCREATE TABLETESTTB ([ID] [int] IDENTITY(1,1)PRIMARY KEY  not NULL, Province[varchar]( -)NULL, City[varchar]( -)NULL)

1. Insert the data and return the inserted data:

INSERT  into TESTTB (province,city) output inserted. Province, inserted. CityVALUES(' Guangdong ',' shenzhen ')

Results returned after execution:

2, similarly, the deletion of data is the same, but the use of deleted table.

Delete  from testtboutput deleted. * where id=1

return Result:

3. Together: Returns both pre-and post-update data:

UPDATETesttbSETProvince= 'Hunan', City='Chenzhou'OUTPUT'I am from (before the update)'+DELETED. Province+DELETED. City as [before],'I'm from (after the update)' +Inserted.province+Inserted.city as [ After]WHEREId=1

return Result:

4. You can also save the returned results in a table variable, which is useful when deleting data and inserting deleted data into the history table

DECLARE @tempTable TABLE(IDint, Provincevarchar( -), Cityvarchar( -) ) DELETE  fromTESTTB OUTPUT deleted.*  into @tempTableWHEREId> 4 SELECT  *  from @tempTable

I hope you apply flexibly!