Exceptions to Full ANSI Compliance
The five exceptions to full ANSI compliance for the Teradata implementation of the MERGE statement are as follows:
In the ANSI definition of the MERGE statement, this clause applies to identity columns only and allows the overriding of either user‑specified or system‑generated identity values. Teradata does not support this operation in its regular non‑error logging MERGE statements, either.
If you specify the UPDATE component before the INSERT component, the order of processing is as follows:
a BEFORE UPDATE triggers
b BEFORE INSERT triggers
c MERGE UPDATE and MERGE INSERT operations
d AFTER UPDATE triggers
e AFTER INSERT triggers
If you place the INSERT specification before the UPDATE specification, the order of processing is as follows:
a BEFORE INSERT triggers
b BEFORE UPDATE triggers
c MERGE INSERT and MERGE UPDATE operations
d AFTER INSERT triggers
e AFTER UPDATE triggers
The lock set for SELECT subquery operations depends on the isolation level for the session, the setting of the AccessLockForUncomRead DBS Control field, and whether the subquery is embedded within a SELECT operation or within a MERGE request.
Transaction Isolation Level |
DBS Control AccessLockForUncomRead Field Setting |
Default Locking Severity for Outer SELECT and Ordinary SELECT Subquery Operations |
Default Locking Severity for SELECT Operations Embedded Within a MERGE Request |
SERIALIZABLE |
FALSE |
READ |
READ |
TRUE |
READ |
||
READ UNCOMMITTED
|
FALSE |
READ |
|
TRUE |
ACCESS |
MERGE requests are also affected by the locking levels set by you or the system. The default locking for MERGE requests is as follows.
The following cases illustrate the effect of these locking levels.