Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
CREATE TRIGGER statementUse the CREATE TRIGGER statement to define a trigger on a table. You can also use CREATE TRIGGER to define an INSTEAD OF trigger on a view.
Using Correlation Names in Triggered Actions
Qualified Versus Unqualified Value

Qualified Versus Unqualified Value

The following table summarizes what value is retrieved when the column name is qualified by the old or by the new correlation name after various trigger events.


Trigger Event	old.column	new.column
INSERT	No value (error)	Inserted value
UPDATE (column updated)	Original value	Current® value (U)
UPDATE (column not updated)	Original value	Original value (N)
DELETE	Original value	No value (error)
SELECT	Original value	No value (error)

When a correlation name has no value, an error is issued only when an SQL or SPL statement referencing the undefined correlation is executed, rather than when the correlation name is declared. Refer to the following key when you read the previous table.

Term: Meaning
Original value: Value before the triggering event
Current value: Value after the triggering event
(N): Cannot be changed by triggered action
(U): Can be updated by triggered actions; updated value might be different from the original value because of preceding triggered actions.

Outside a FOR EACH ROW triggered-action list, you cannot qualify a column from the triggering table with either the old correlation name or the new correlation name; it always refers to the current value in the database.

Statements in the trigger action list use whatever collating order was in effect when the trigger was created, even if a different collation is in effect when the trigger action is executed. See SET COLLATION statement for details of how to specify a collating order different from what DB_LOCALE specifies.