PL/SQL CREATE TRIGGER

In Oracle PL/SQL, a trigger is a set of instructions that are automatically executed (or “triggered”) in response to a specific event on a particular table or view. The CREATE TRIGGER statement is used to define a new trigger in Oracle Database. Triggers are commonly used to enforce business rules, perform complex data validation, or automate certain tasks when specific events occur.

Syntax

Here is the basic syntax for creating a trigger in Oracle PL/SQL:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE | {INSERT | UPDATE | DELETE}}
ON table_name
[REFERENCING {OLD AS old | NEW AS new} [FOR EACH ROW]]
WHEN (condition)
DECLARE
   -- Declaration of local variables
BEGIN
   -- Trigger body: PL/SQL statements to be executed
END;
/

Let’s break down the key components of the CREATE TRIGGER statement:

CREATE OR REPLACE TRIGGER: This clause is used to create a new trigger or replace an existing trigger with the same name.

trigger_name: This is the name of the trigger, which should be unique within the schema.

BEFORE | AFTER | INSTEAD OF: Specifies when the trigger should be fired. BEFORE triggers are executed before the triggering event, AFTER triggers are executed after the event, and INSTEAD OF triggers are used with views to replace the triggering event.

{INSERT | UPDATE | DELETE}: Specifies the event that triggers the execution of the trigger. You can use multiple events separated by commas.

ON table_name: Specifies the table on which the trigger is defined.

REFERENCING {OLD AS old | NEW AS new} [FOR EACH ROW]: This clause is used to reference the old and new values of the row being affected by the triggering event. It is commonly used in UPDATE and DELETE triggers.

WHEN (condition): This optional clause allows you to specify a condition under which the trigger should be executed. If the condition evaluates to true, the trigger is fired; otherwise, it is skipped.

DECLARE: This is the beginning of the optional declaration section, where you can declare local variables that are used within the trigger body.

BEGIN and END: These delimit the trigger body, where you place the PL/SQL statements that are executed when the trigger is fired.

Example

Here’s a simple example of a BEFORE INSERT trigger that sets a default value for a column if it is not provided during the insertion of a new record:

CREATE OR REPLACE TRIGGER before_insert_trigger
BEFORE INSERT
ON your_table
FOR EACH ROW
BEGIN
   IF :NEW.column_name IS NULL THEN
      :NEW.column_name := 'default_value';
   END IF;
END;
/

This trigger checks if the value for column_name is null in the new row being inserted. If it is null, it sets a default value of ‘default_value’.