PL/SQL Trigger 1 – Introduction

Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database, or they can be written as C callouts.

Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

Full syntax description of the CREATE TRIGGER statement

CREATE [OR REPLACE] TRIGGER schema.trigger-name
{BEFORE | AFTER} dml-event ON table-name
[FOR EACH ROW]
[DECLARE ...]
BEGIN
  — Your PL/SQL code goes here.
[EXCEPTION ...]
END;
/

 

An example

CREATE OR REPLACE TRIGGER my_test_trg
BEFORE INSERT OR UPDATE OR DELETE ON my_table
FOR EACH ROW
BEGIN
  — Flags are booleans and can be used in any branching construct.
  CASE
    WHEN INSERTING THEN
      — Include any code specific for when the trigger is fired from an INSERT.
    WHEN UPDATING THEN
      — Include any code specific for when the trigger is fired from an UPDATE.
    WHEN DELETING THEN
      — Include any code specific for when the trigger is fired from an DELETE.
  END CASE;
END;
/

 


 

You can leave a response, or trackback from your own site.

Leave a Reply