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.
- Before Trigger
- After Trigger
a) Before trigger
Here action first, event next.
b) After Trigger
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
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
Create 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);)
update 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: