The MERGE statement combines the four separate DML statements (SELECT, INSERT, UPDATE, and DELETE) involved in a merge operation. The actual internal implementation of MERGE is the very same as the distinct DML statements it encapsulates. This means that upgrading the existing installations and then using MERGE statement with the newly created or existing deliverables wont break any code.
All AFTER and INSTEAD OF triggers that have already been defined in existing tables or updateable views continue to fire when those tables or updatable views are designated as the target of a MERGE statement. For example, the WHEN NOT MATCHED THEN...INSERT clause fires any insert triggers defined for the target; similarly, the WHEN MATCHED THEN...DELETE clause fires delete triggers. The end result is that triggers are fired just the same as they would be using separate statements instead of MERGE. Existing business logic, constraints, and rules all continue to function as they did before.
The MERGE statement also supports the same OUTPUT clause introduced in SQL Server 2005 for the INSERT, UPDATE, and DELETE statements. This clause returns change information from each row affected by DML operations in the same INSERTED and DELETED pseudo-tables exposed by triggers. Being able to capture this information in the OUTPUT clause is a much better choice than capturing it in triggers, since triggers introduce nondeterministic behavior—that is, you cannot guarantee that multiple triggers on the same table will consistently fire in the same order every time, and that is often the cause of subtle bugs that are very difficult to track down.
In addition to INSERTED and DELETED pseudo-table columns, a new virtual column named $action has been introduced for OUTPUT when used with the MERGE statement. The $action column will return one of the three string values—'INSERT', 'UPDATE', or 'DELETE'—depending on the action taken for each row.
References: Microsoft Press Books
Article 
No comments:
Post a Comment