Triggers are a fundamental part of PL/SQL and are special types of stored programs that are automatically executed or fired in response to specific events on a particular table or view.
Triggers are typically created using the CREATE TRIGGER statement. Once created, a trigger is enabled by default, meaning it will be automatically executed when the specified event occurs. However, if a trigger is disabled, it won’t be executed until explicitly enabled.
To enable a trigger, you can use the ENABLE keyword:
ALTER TRIGGER trigger_name ENABLE;
Syntax of Creating a Trigger
The basic syntax for creating a trigger in PL/SQL is as follows:
CREATE OR REPLACE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW DECLARE -- Declarations section BEGIN -- Trigger logic here END; /
Let’s break down the syntax:
CREATE OR REPLACE TRIGGER: This is the statement used to create a new trigger or replace an existing one with the same name.
trigger_name: This is the name you give to the trigger.
BEFORE/AFTER INSERT/UPDATE/DELETE: This part specifies the triggering event and the timing of the trigger execution.
ON table_name: Specifies the table on which the trigger is defined.
FOR EACH ROW: Indicates that the trigger is a row-level trigger, meaning it will be executed once for each row affected by the triggering statement.
DECLARE: This is the declaration section where you can declare variables and define other elements.
BEGIN: This is the beginning of the executable section where you write the actual PL/SQL code for the trigger logic.
END; This marks the end of the trigger code.
Remember that triggers should be used judiciously, as improper use can lead to performance issues and unintended consequences.