Trigger Blocks : Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Trigger

Triggers are used to define code that is executed when certain actions or events occur.

The Syntax for Creating a Database Trigger

CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE|AFTER} verb_list ON table_name
    [[REFERENCING correlation_names] FOR EACH ROW [WHEN (condition)]]

DECLARE
    declarations
BEGIN
    pl/sql_code
END;

verb_list -- The SQL verbs that fire the trigger.

table_name -- The table on which the trigger is defined.

correlation_names -- Allows you to specify correlation names other than the default of OLD and NEW.

condition -- An optional condition placed on the execution of the trigger.

declarations -- Consists of any variable, record, or cursor declarations needed by this PL/SQL block.

SQL> CREATE TABLE to_table
  2  (col1 NUMBER);

Table created.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER before_statement_trigger
  2  BEFORE INSERT ON to_table
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('Before Insert Statement Level');
  5  END;
  6  /

Trigger created.

SQL>
SQL> drop table to_table;

Table dropped.

SQL>

SQL>
SQL> CREATE TABLE to_table
  2  (col1 NUMBER);

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER before_row_trigger
  2  BEFORE INSERT ON to_table
  3  FOR EACH ROW
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE('Before Insert Row Level');
  6  END;
  7  /

Trigger created.

SQL>
SQL> drop table to_table;

Table dropped.

SQL>
SQL>