Triggers Teradata

A trigger is an event driven maintenance operation. The event is caused by a modification to one or more columns of a row in a table. The original modification request, the trigger and all subsequent triggers constitute a single transaction. This means that the SQL and all triggered statements must work successfully, or else, all fail and rollback all changed rows from the Transient Journal.

Triggers are an excellent way to automate many database modifications that previously required manual intervention by the user or DBA. Since triggers occur automatically, they remove the burden from a person's memory to make the subsequent updates in one or more other tables. Therefore, they can be used as a labor savings device and improve data integrity at the same time, two benefits in one.

Triggers can be particularly useful when Referential Integrity (RI) is defined. Teradata incorporates the Prevent logic as its strategy for handling any SQL request that attempts to modify a column with new data that violates RI. Triggers can be established to implement either the Cascade or Nullify strategies.

Triggers are a new form of object in the Teradata database. They are designated with a type of "G" seen with a HELP DATABASE request. My guess is that the developers could not use "T" because of tables and the "RI" reminded them of referential integrity, so "G" was the next character in TRIGGER. The DDL statements used with triggers are: CREATE, DROP, REPLACE, ALTER, HELP and SHOW. These statements manage trigger usage and maintenance. The appropriate database privileges are needed to use any of these commands.

Terminology

Triggering Statement

The user's initial SQL maintenance request that causes a row to change in a table and then causes a trigger to fire (execute).

It can be: INSERT,
UPDATE,
DELETE,
INSERT/SEL
ECT

It cannot be: SELECT

Triggered Statement

It is the SQL that is automatically executed as a result of a triggering statement.

It can be: INSERT,
UPDATE,
DELETE,
INSERT/SELECT
,
ABORT/ROLLBA
CK, EXEC

It cannot be: BEGIN/END
TRANSACTION,
COMMIT,
CHECKPOINT,
SELECT

Logic Flow

The operations performed by a trigger are defined in the Data Dictionary (DD) and stored as an SQL object. Whenever a user issues any SQL request that modifies a row, a column or table that has a trigger defined on it, the trigger SQL is eligible to also execute.

It becomes the "memory" for all subsequent update operations without a person needing to remember to make the appropriate changes on one or more other tables.

The original user SQL is the triggering statement on the Subject table. The subsequent, automatic update to a different table is the triggered statement.

Logic Flow



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics