TRIGGERS

TRIGGERS

 

Oracle allows you to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These procedures are called database triggers.

Triggers can be defined only on tables, not on views. However, triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.

 

In addition, triggers are commonly used to

automatically generate derived column values

prevent invalid transactions

enforce complex security authorizations

enforce referential integrity across nodes in a distributed database

enforce complex business rules

provide transparent event logging

provide sophisticated auditing

maintain synchronous table replicates

gather statistics on table access

A trigger has three basic parts:

a triggering event or statement

a trigger restriction

a trigger action

 

Types of Triggers

-Row Triggers

-Statement Triggers

When a CREATE TRIGGER statement commits()or a ddl statement runs, the compiled PL/SQL code, called P code (for pseudocode), is stored in the database and the source code of a trigger is flushed from the shared pool.

A trigger that fires at row level can access the data in the row that it is processing by using correlation names. The default correlation names are OLD, NEW, and PARENT. To change the correlation names, use the REFERENCING clause of the CREATE TRIGGER statement.

OLD, NEW, and PARENT are also called pseudorecords.

The structure of a pseudorecord is table_name%ROWTYPE, where table_name is the name of the table on which the trigger is created (for OLD and NEW) or the name of the parent table (for PARENT).

:pseudorecord_name.field_name

The restrictions on pseudorecords are:

-A pseudorecord cannot appear in a record-level operation. For example, the trigger cannot include this statement:

:NEW := NULL;

-The trigger cannot change OLD field values.

-If the triggering statement is DELETE, then the trigger cannot change NEW field values. Because there is no New field value.

-An AFTER trigger cannot change NEW field values, because the triggering statement runs before the trigger fires.

A BEFORE trigger can change NEW field values before a triggering INSERT or UPDATE statement puts them in the table.

If a statement triggers both a BEFORE trigger and an AFTER trigger, and the BEFORE trigger changes a NEW field value, then the AFTER trigger “sees” that change.

trigger1.png

 

CREATE OR REPLACE TRIGGER t

BEFORE

INSERT OR

UPDATE OF salary, department_id OR

DELETE

ON employees

BEGIN

CASE

WHEN INSERTING THEN

DBMS_OUTPUT.PUT_LINE(‘Inserting’);

WHEN UPDATING(‘salary’) THEN

DBMS_OUTPUT.PUT_LINE(‘Updating salary’);

WHEN UPDATING(‘department_id’) THEN

DBMS_OUTPUT.PUT_LINE(‘Updating department ID’);

WHEN DELETING THEN

DBMS_OUTPUT.PUT_LINE(‘Deleting’);

END CASE;

END;

/

 

CREATE OR REPLACE TRIGGER log_salary_increase

AFTER UPDATE OF salary ON employees

FOR EACH ROW

BEGIN

INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)

VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, ‘New Salary’);

END;

/

 

CREATE OR REPLACE TRIGGER print_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON employees

FOR EACH ROW

WHEN (NEW.job_id <> ‘AD_PRES’)  — do not print information about President

DECLARE

sal_diff  NUMBER;

BEGIN

sal_diff  := :NEW.salary  – :OLD.salary;

DBMS_OUTPUT.PUT(:NEW.last_name || ‘: ‘);

DBMS_OUTPUT.PUT(‘Old salary = ‘ || :OLD.salary || ‘, ‘);

DBMS_OUTPUT.PUT(‘New salary = ‘ || :NEW.salary || ‘, ‘);

DBMS_OUTPUT.PUT_LINE(‘Difference: ‘ || sal_diff);

END;

/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s