in black and white
Main menu
Share a book About us Home
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 46 47 48 49 50 51 < 52 > 53 54 55 56 57 58 .. 218 >> Next

MERGE-INTO (single row) The target table in a MERGE-INTO statement may be defined with an identity column. The system will generate numbers for MERGE-INTO inserts similar to singleton INSERTs. For more details, see Teradata SQL Reference, Volume 6.

2 - 10

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS



A trigger is one or more SQL action statements, along with the parameters that trigger the action, that are stored as a named object and associated with a table.

The statements are executed, or fired, when some event, called a triggering event, occurs. Sometimes a statement fires a trigger which, in turn, fires another trigger. Thus, the outcome of one triggering event can itself become another trigger.

This section provides an overview of triggers functionality and restrictions. For more detailed information, see:

Introduction to Teradata RDBMS


Teradata RDBMS Database Design

Firing Triggers

Triggers execute when an INSERT, UPDATE, or DELETE touches one or more specified columns in the subject table. Typically, the trigger statements perform an action on a table that is different from the subject table.

A trigger is one of two mutually exclusive types:

Type Description
Statement trigger Fires once per triggering statement.
Row trigger Fires once per each row changed by the triggering statement.

The Teradata RDBMS processes and optimizes the triggered and the triggering statements in parallel to maximize system performance.

Trigger Functionality

Triggers impose only row hash locks on their target tables, so most of the table remains accessible.

You can use triggers to:

Define a trigger on the parent table to ensure that UPDATEs and DELETEs performed on a parent table are propagated to the child table.

Control massive UPDATEs, INSERTs, or DELETEs during business hours. For example, you can use triggers to:

Create a purchase order when the inventory drops below a specified threshold

Teradata RDBMS Database Administration

2 - 71 Chapter 2: Building the Teradata RDBMS


Set thresholds for inventory of each item by store

Change a price if the daily volume does not meet expectations

Note: To schedule statement execution based on other parameters, such as user or account, resource priority, and so forth, see "Scheduling Workloads with Teradata Dynamic Query Manager (TDQM)" on page 15-12.

Use triggers for auditing. For example, you can define a trigger which causes INSERTs in a log record when an employee receives a raise higher than 10%.

Restrictions on Using Triggers

If your applications use triggers, you should be aware of the following:

You can define triggers for base tables only. Triggers are supported for all base tables, whether they have a PPI or a NPPI.

With an identity column, you cannot create or replace a trigger definition if:

Its triggering statement is an INSERT on a table that has an identity column

The triggered statement or WHEN clause of the FOR EACH clause of the trigger definition references that identity column

You cannot combine row and statement operations within a single trigger definition.

You cannot define a trigger and a join index on the same table.

Disable triggers with ALTER TABLE before running the FastLoad or MultiLoad utility on tables with triggers. (Triggers are supported by the TPump utility.)

2 - 72

Teradata RDBMS Database Administration 2 - 71 Chapter 2: Building the Teradata RDBMS



Teradata macros are SQL statements or multi-statement transactions that are stored as a database object under a unique name. You execute the stored statements by submitting the SQL EXECUTE MACRO macroname statement. The privileges required on the underlying tables are verified for the user submitting the EXECUTE MACRO statement.

IF you want to ... THEN use the statement .
define and store a new macro CREATE MACRO
run the SQL statements within the EXECUTE MACRO
change the parameter values within the macro REPLACE MACRO
remove a macro DROP MACRO Note: Be careful that applications do not reference the macro being dropped.

The advantages of using macros include:

Generation of less channel and network traffic

Easy execution of frequently-used SQL operations

The transactions within the macro can be defined to:

Enforce data integrity rules

Provide data security

For more detailed information on macros, see:

"Limiting Data Access with Macros" on page 6-43

Teradata RDBMS Database Design

"CREATE MACRO" in Teradata RDBMS SQL Reference, Volume 4

Teradata RDBMS SQL Reference, Volume 1 and Volume 3

Teradata RDBMS Database Administration

2 - 73 Chapter 2: Building the Teradata RDBMS



A view is a virtual table created by defining a SELECT statement on one or more base tables and/or other views. When a user references a view, the response returns the rows selected according to the CREATE VIEW viewname AS ... SELECT FROM ... definition. Thus, you can think of a view as a dynamic window to its underlying tables.
Previous << 1 .. 46 47 48 49 50 51 < 52 > 53 54 55 56 57 58 .. 218 >> Next