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.
- ROW Level
- 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
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