Triggers are associated with response-based events such as a Database Definition Language (DDL) statement such as CREATE, DROP or ALTER or a Database Manipulation Language (DML) statement such as UPDATE, INSERT or DELETE or any other database operation such as a Startup, Shutdown, Logging in and Logging Out.
A trigger therefore helps to prevent invalid transactions, duplication of tables/data, forced referential integrity and provides security authorizations.
The parts of a PL/SQL trigger are SQL statements of a trigger, trigger action statement and trigger restrictions.
Creating PL/SQL Triggers
Syntax
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Create or Replace Trigger Trigger_Name Before or After or Instead of Insert or Update or Delete of Column_Name on Table_Name [Referencing OLD AS O New AS N] For Each Row When (Condition) Declare Declaration Section Begin Execution Section End; |
Create or Replace Trigger Trigger_Name: 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 Column_Name: This statement is only used with triggers who have Update event and when a specific column is updated.
On Table_Name: 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 record
For 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. This helps the trigger to be executed only when the specified condition evaluated to be TRUE.
Types of Triggers in PL/SQL
There 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 Example
Here, we have created a trigger which ensures that we don’t enter any Admin who has a salary less than 1000.
|
1 2 3 4 5 6 7 8 |
create or replace trigger UpdateSalary before insert or update on Admin for each row when(new.asal<1000) begin raise_application_error(-20189,'Salary Must be Greater than 1000'); end; / |
Output
To check whether this trigger works perfectly or not, we try to insert new value into the database.
|
1 |
insert into Admin values(5, 'Vedant Mishra', 500); |
Output
PL/SQL Trigger Commands
Enable a Trigger
If a trigger is disabled, you can alter/change it to enable it to perform the desired actions.
Syntax
|
1 |
Alter Trigger Trigger_Name Enable; |
Drop a Trigger
If you want to delete a trigger from the system, you can use the drop command.
Syntax
|
1 |
Drop Trigger Trigger_Name; |
Disable a Trigger
If you don’t want a trigger to execute itself for a while without deleting it from the system or database, you can alter/change its value by disabling it.
Syntax
|
1 |
Alter Trigger Trigger_Name Disable; |
