SQL Server 2005 returns the modified data

Today in the company to see colleagues wrote a SQL2005 of the new characteristics of the article, feel very practical, here and share with you. The original copyright does not belong to me.

This technique is mainly used for inserted and deleted virtual tables, and these two tables are believed to be familiar. In the past we used it primarily in triggers.

Let's review the two tables first:

The tables inserted and deleted are logical tables, and the tables are managed by the system, stored in memory, not stored in the database, and therefore are not allowed to be modified directly by the user.

The structure of the two tables is in the same table structure as the table that is acting on the trigger. These two tables are dynamically resident in memory, and they are also deleted when the triggers have finished working.

Next, look at our case, in fact, it is very simple to use, but also very practical. In addition to using the above two tables, he also used output parameters.

To create a table:

IF EXISTS (SELECT * from sys.objects WHERE object_id = object_id (n ' [TESTTB] ') and type in (n ' U '))

DROP TABLE TESTTB

CREATE TABLE TESTTB (

[ID] [int] IDENTITY (1,1) PRIMARY KEY not NULL,

province [varchar] (m) NULL,

City [varchar] (m) NULL

)

1, insert the data, and return the inserted data:

INSERT into TESTTB (province,city)

Output inserted. Province, inserted. City

VALUES (' Guangdong ', ' Shenzhen ')

The result returned:

2, the same, delete data is the same, but the use of deleted table Bale.

Delete from TESTTB

Output deleted.*

where id=1

return Result:

3, two together: return before and after the updated data:

UPDATE testtb SET province = ' Hunan ', city= ' Chenzhou '

OUTPUT ' I come from (update before) ' + DELETED. Province+deleted. City as [before], ' I come from (updated) ' + inserted.province+inserted.city as [after]

WHERE id=1

return Result:

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

DECLARE @tempTable TABLE (

ID int,

Province varchar (50),

City varchar (50)

)

DELETE from TESTTB OUTPUT deleted.* to @tempTable

WHERE ID > 4

SELECT * from @tempTable

I hope everyone should be flexible!