Archive for April, 2013

PL/SQL Trigger 3 – BEFORE and AFTER UPDATE Trigger

BEFORE UPDATE Trigger

A BEFORE UPDATE Trigger means that Oracle will fire this trigger before the UPDATE operation is executed.

The syntax for an BEFORE UPDATE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE UPDATE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   — variable declarations

BEGIN
   — trigger code

EXCEPTION
   WHEN …
   — exception handling

END;

Restrictions

    * You can not create a BEFORE trigger on a view.
    * You can update the :NEW values.
    * You can not update the :OLD values.

Read more »

PL/SQL Trigger 2 – BEFORE and AFTER INSERT Trigger

BEFORE INSERT Trigger

A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.

The syntax for an BEFORE INSERT Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   — variable declarations

BEGIN
   — trigger code

EXCEPTION
   WHEN …
   — exception handling

END;

Restrictions

    * You can not create a BEFORE trigger on a view.
    * You can update the :NEW values.
    * You can not update the :OLD values.

Read more »

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;
/

 

Read more »