what are Triggers?
Share
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
A trigger is an SQL statement that is executed automatically by the DBMS as a side effect of a modification – the triggering event – to a named table.
The act of executing a trigger is sometimes known as firing the trigger.
Some Use Cases :
• validating input data and maintaining complex integrity constraints.
• supporting alerts that action needs to be taken when a table is updated in some way.
• refreshing derived attributes after an update operation.
• maintaining audit information, by recording the changes made, and by whom.
• supporting replication.
The basic format of the CREATE TRIGGER statement is as follows:
CREATE TRIGGER TriggerName
BEFORE | AFTER | INSTEAD OF <triggerEvent> ON <TableName>
[REFERENCING <oldOrNewValuesAliasList>]
[FOR EACH {ROW | STATEMENT}]
[WHEN (triggerCondition)]
<triggerBody>
• Triggering events include insertion, deletion, and update of rows in a table. In the latter case only, a triggering event can also be set to cover specific named columns of a table.
• A trigger has an associated timing of either BEFORE, AFTER, or INSTEAD OF.
• A BEFORE trigger is fired before the associated event occurs
• An AFTER trigger is fired after the associated event occurs
• An INSTEAD OF trigger is fired in place of the trigger event.
Three different types of Triggers
Row-Level Triggers (FOR EACH ROW) • execute for each row of the table that is affected by the triggering event
Statement-Level Triggers (FOR EACH STATEMENT) • execute only once even if multiple rows are affected by the triggering event.
Cascading Triggers .Triggers can also activate themselves one after the other.
This can happen when the trigger action makes a change to the database that has the effect of causing another event that has a trigger associated with it to fire.