Triggers in TeraData

Triggers

Perform actions based on the event, that event is inserting, delete, update operations on tables.

Based on the actions, there are 2 types of triggers.

• Before Trigger
• After Trigger

a) Before trigger

Here action first, event next.

b) After Trigger

The event first, action NEXT.

Trigger applied at  2 Levels.

1. ROW Level
2. Statement Level

Note - In Tera data V2R5.1 onwards before trigger does not support

Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training

Example

Create trigger< trigger name>()

Replace trigger< trigger name>()

Drop trigger< trigger name>

Help trigger< trigger name>

SHOW trigger< trigger name>

Alter trigger< trigger name>DISABLED

ALTER trigger< trigger name>ENABLED

Create trigger< trigger name> Alter update of (column name)

ON<Table Name>Referencing OLD TALE AS OLD TABLE NEW VABLE AS NEW TABLE.

For each statement/Row

[Where condition]

(Trigger Action)

Example

Create Trigger Vinay after update of (party income) on party for each statement

(Insert into test(11,’Record update’,’00001’););

SHOW Trigger Vinay

Update party set party income=99999 where party id=2

Teradata Interview Questions

Example

Create table Translog

OLDVALU INTEGER,

NEW VALUE  INTEGER,

Record date format’ yyyy-mm-dd’ USE-UPDATED

Varchar (50)Default user

Create trigger Tr log

After update of(party income) on party

Referencing OLD AS O New AS N For Each Row

When(N. Party name>10000)

(Insert into translog (o. party income, N. party income, current-date);)

update party set party income=40000 where party id =2

Limitations

We cannot use the below statement in the trigger body

Select statement to return as Result

Exception Handling

BEGIN And END Statements

COMMIT

DDL And DCL Statements

For In-depth knowledge on Teradata click on:

• BTEQ Features in Teradata
• Secondary Index in Teradata
• Memory Management in Teradata
• Types of OLAP’s in Teradata
• TPUMP Structure and Process In Teradata