MERGE is what is known as a deterministic statement.
That is, you can only update the same row of the target table once in the same MERGE statement.
But, you may be wondering, why are triggers so hated? They can be hundreds of lines long and contain convoluted conditions.
Many developers dislike them because they fire silently in the background, seeming to live just beyond the fingertips of their control.
These trigger types are referred to as "Schema-level triggers". Performing conditional actions in triggers (or testing data following modification) is done through accessing the temporary Inserted and Deleted tables. The following functionality in SQL:2003 was previously not implemented in Postgre SQL: Firebird supports multiple row-level, BEFORE or AFTER, INSERT, UPDATE, DELETE (or any combination thereof) triggers per table, where they are always "in addition to" the default table changes, and the order of the triggers relative to each other can be specified where it would otherwise be ambiguous (POSITION clause.) Triggers may also exist on views, where they are always "instead of" triggers, replacing the default updatable view logic.
(Before version 2.1, triggers on views deemed updatable would run in addition to the default logic.) Firebird does not raise mutating table exceptions (like Oracle), and triggers will by default both nest and recurse as required (SQL Server allows nesting but not recursion, by default.) Firebird's triggers use NEW and OLD context variables (not Inserted and Deleted tables,) and provide UPDATING, INSERTING, and DELETING flags to indicate the current usage of the trigger.
Imagine you have a huge table – 1 million rows – and you need to update a column. If you have triggers on the table, not even Nostradamus knows how many records across the database were really updated. Triggers are also a convenient hiding place for bugs. Tom Kyte points out another reason to be wary of triggers. And it’s not just utl_mail; the same thing would happen with utl_file, utl_http, and many other utl_ functions.
What's more, we can wrap up the whole transformation process into this one Oracle MERGE command, referencing the external table and the table function in the one command as the source for the MERGED Oracle data.
file,10) full (contracts_file) */ * from contracts_file ))) f on d.contract_id = f.contract_id when matched then update set desc = f.desc, init_val_loc_curr = f.init_val_loc_curr, init_val_adj_amt = f.init_val_adj_amt when not matched then insert values ( f.contract_id, f.desc, f.init_val_loc_curr, f.init_val_adj_amt); In Oracle Database 10g, the MERGE statement has been extended to cover a larger variety of complex and conditional data transformations, allowing faster loading of large volumes of data.
The MERGE statement is designed to combine multiple operations to reduce the complexity of mixed insert and update operations.
MERGE allows you to avoid multiple INSERT, UPDATE, and DELETE DML statements by combining the operations into a single statement.