Trigger In Pl Sql Program
Triggers In Oracle Sql
PL/SQL triggers are block structures or pre-defined programs, which may be in-built or even explicitly developed by the programmers for a particular task. End;Create or Replace Trigger TriggerName: This statement creates a trigger with the given name or overwrites an existing trigger with the same name.Before or After or Instead of: This statement specifies the time at which the trigger should be executed i.e., either before or after updating/Inserting/deleting the values in a table.Insert or Update or Delete: This specifies the DML operation. This statement determines the triggering event. One or more triggering event can be used together if needed. The trigger gets fired at all the specified triggering event.Of ColumnName: This statement is only used with triggers who have Update event and when a specific column is updated.On TableName: This statement specifies the name of the view or table with which the particular trigger has to be associated.Referencing OLD AS O New AS N: This statement allows us to refer Values (Old and New) for Data Manipulation language (DML) statements such as Delete, Insert or Update. This statement is optional and is useful for referring the new and old values of the data that needs to be changed. It is not possible to refer an old value when inserting a new record or to refer to new value when deleting an old recordFor Each Row: This statement is used to specify whether the trigger being developed is a row level trigger (each row gets influenced) or a statement level trigger or a table level trigger (executes just once when the PL/SQL statement is executed).When (Condition): This statement is valid only for a row level trigger.
Compound Trigger In Oracle
This helps the trigger to be executed only when the specified condition evaluated to be TRUE.Types of Triggers in PL/SQLThere are two types of basic triggers in PL/SQL. There can be a number of combinations of triggers as and when needed.Row level trigger: Here, the event is executed for each row which may insert, delete or update a data.Statement level trigger: Here, the event is executed foreach SQL statement or a complete table.PL/SQL Triggers ExampleHere, we have created a trigger which ensures that we don’t enter any Admin who has a salary less than 1000.