4 min læsning

Output row values effected by an update

Featured Image

Have you ever thought about logging the before and after value for an update or delete? It’s actually pretty simple getting these information as a return on an update and delete.

Lets make a quick example.

First we need to create a test table an populate a couple of rows for testing purpose.

<span style="color:blue;">CREATE TABLE </span><span style="color:teal;">Person
</span><span style="color:gray;">(
    </span><span style="color:teal;">PersonID </span><span style="color:blue;">INT IDENTITY</span><span style="color:gray;">(</span>1<span style="color:gray;">,</span>1<span style="color:gray;">) </span><span style="color:blue;">PRIMARY KEY</span><span style="color:gray;">,
    </span><span style="color:teal;">Name </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">)
)

</span><span style="color:blue;">INSERT INTO </span><span style="color:teal;">Person </span><span style="color:gray;">(</span><span style="color:teal;">Name</span><span style="color:gray;">) </span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'Peter'</span><span style="color:gray;">)
</span><span style="color:blue;">INSERT INTO </span><span style="color:teal;">Person </span><span style="color:gray;">(</span><span style="color:teal;">Name</span><span style="color:gray;">) </span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'Jens'</span><span style="color:gray;">)
</span><span style="color:blue;">INSERT INTO </span><span style="color:teal;">Person </span><span style="color:gray;">(</span><span style="color:teal;">Name</span><span style="color:gray;">) </span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'Jacob'</span><span style="color:gray;">)
</span><span style="color:blue;">INSERT INTO </span><span style="color:teal;">Person </span><span style="color:gray;">(</span><span style="color:teal;">Name</span><span style="color:gray;">) </span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'Morten'</span><span style="color:gray;">)

</span><span style="color:blue;">SELECT </span><span style="color:gray;">* </span><span style="color:blue;">FROM </span><span style="color:teal;">Person
</span>

Now lets change the second row. The update we are using are changing the name from Jens to Jens Jensen and returning both the new (Jens Jensen) and the old name (Jens).

<span style="color:magenta;">UPDATE </span><span style="color:teal;">Person
</span><span style="color:blue;">SET        </span><span style="color:teal;">Name </span><span style="color:gray;">= </span><span style="color:red;">'Jens Jensen'
</span><span style="color:blue;">OUTPUT  </span><span style="color:teal;">inserted</span><span style="color:gray;">.</span><span style="color:teal;">Name </span><span style="color:blue;">AS NewName</span><span style="color:gray;">,
        </span><span style="color:teal;">deleted</span><span style="color:gray;">.</span><span style="color:teal;">Name </span><span style="color:blue;">AS </span><span style="color:teal;">OldName
</span><span style="color:blue;">WHERE    </span><span style="color:teal;">Name </span><span style="color:gray;">= </span><span style="color:red;">'Jens'

</span>

As you can see the keyword output return inserted and deleted values.

You can also use OUTPUT as a part of a DELETE statement. Combined with some extra TSQL commands you have a perfect output for a Log table.

<span style="color:blue;">DELETE    FROM </span><span style="color:teal;">Person
</span><span style="color:blue;">OUTPUT    </span><span style="color:teal;">deleted</span><span style="color:gray;">.</span><span style="color:teal;">PersonID </span><span style="color:blue;">AS </span><span style="color:teal;">DeletedPersonID</span><span style="color:gray;">, </span><span style="color:teal;">deleted</span><span style="color:gray;">.</span><span style="color:teal;">Name </span><span style="color:blue;">AS </span><span style="color:teal;">DeletedPersonName</span><span style="color:gray;">,
        </span><span style="color:magenta;">SYSTEM_USER </span><span style="color:blue;">AS </span><span style="color:teal;">DeletedBy</span><span style="color:gray;">, </span><span style="color:magenta;">SYSDATETIME</span><span style="color:gray;">() </span><span style="color:blue;">AS </span><span style="color:teal;">DeleteTime
</span><span style="color:blue;">WHERE    </span><span style="color:teal;">Name </span><span style="color:gray;">= </span><span style="color:red;">'Peter'

</span>

Enjoy