Oracle PL/SQL Tutorial - PL/SQL Triggers

• « Previous
• Next »

Triggers are PL/SQL programs to execute in response to a particular event on a table.

The events in question can take place FOR EACH ROW or for a SQL statement.

There are row-level or statement-level triggers.

The actual events associated with triggers can take place BEFORE, AFTER, or INSTEAD OFan INSERT, an UPDATE, or a DELETE SQL statement.

Example

The following code shows a Trigger Against the Authors Table.

CREATE OR REPLACE TRIGGER authors_bir 
BEFORE INSERT ON authors 
FOR EACH ROW 
BEGIN 
    if upper(:new.name) = 'Mary' then 
        raise_application_error(20000, 'Sorry, that name is not allowed.'); 
    end if; 
END; 
/ 

Note

The syntax used is as follows:

CREATE [OR REPLACE] TRIGGER <trigger_name> 
BEFORE INSERT ON <table_name> 
FOR EACH ROW 
BEGIN
 <pl/sql> 
END; 

<trigger_name> is the name of the trigger, <table_name> is the name of the table, <pl/sql> is the PL/SQL program you've written to be executed BEFORE someone INSERTs EACH ROW.

The brackets [ ] around the OR REPLACE keyword denote that it is optional.

The OR REPLACE clause will allow you to re-create your trigger if it already exists.

• Next »
• « Previous