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 trigger.

  1. Before Trigger
  2. After Trigger

a) Before trigger

Here action first, event next.

b) After Trigger

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

Desired to gain proficiency on Teradata? Explore the blog post on Teradata Training Online

to become a pro in Teradata.

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

2Create trigger< trigger name> Alter up date 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 updat’,’00001’););

SHOW Trigger vinay

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

Learn more about Teradata Interview Questions in this blog post.

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 trans log (o. party income, N. party income, current-date);)

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

Limitations

We cannot use below statement in 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: