Использование оператора OUTPUT для отслеживания изменений DML

  • Article

?????? ?? ??, ???…

?? ?????? ??????????? ????????? ?????? ??? ????????????? ?????????:

? SQL Server 2005 ???????? OUTPUT ???????? ?????? ?????????? DML ???????????.

????????:

INSERT

    [ TOP ( expression ) [ PERCENT ] ]

    [ INTO ]

    { <object> | rowset_function_limited

      [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]

    }

{

    [ ( column_list ) ]

     [ <OUTPUT Clause> ]

    { VALUES ( ( { DEFAULT | NULL | expression } [ , ...n ] ) [ , ...n ] )

    | derived_table

    | execute_statement

    | <dml_table_source>

    | DEFAULT VALUES

    }

}

???????? OUTPUT ????? ????????? ?????????????? ????????? ????? ?????????? DML ?????????? ? ??????? ??? ?????????? ???? ???????.

????????, ????????? ??? ??????????? (??? ?????????) ??????.

????????????? ??? ?????? ?? ????????????? ??????? INSERTED ? DELETED, ???????????? ??????????.

?????? :

--???????? ??????? Address

Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))

--??????? ?????? ? ???????

Insert into Address Values (234,567,'

1234 One SQL Way

, Microsoft City, U.S.')

Insert into Address Values (345,678,'

1234 One Windows Way

, Microsoft City, WA')

--??????? ????????? ??????????

Declare @Recordchanges table (change Varchar(255))

--??????? ?????? ? ???????

Update Address

Set Address=reverse(address)

--??????? ????????? ? ????????? ??????????

OUTPUT '??????????? ??????:' + DELETED.Address+' ???????? ??: '+ INSERTED.Address+'' into @RecordChanges

--?????? ????????? ?? ????????? ??????????

Select * from @RecordChanges

?? ?????? ?????:

Change

------------------------

??????????? ??????:'1234 One SQL Way, Microsoft City, U.S.' has been ???????? ??: '.S.U ,ytiC tfosorciM ,yaW LQS enO 4321'

??????????? ??????:'1234 One Windows Way, Microsoft City, WA' has been ???????? ??: 'AW ,ytiC tfosorciM ,yaW swodniW enO 4321'