ORACLE PROJECTS: PL/SQL Triggers

#=======================================================================

# Writer : Lokanadham Thandlam

#=======================================================================

--Example 9–1 TRIGGER Uses Conditional Predicates TO Detect Triggering STATEMENT

CREATE OR REPLACE TRIGGER t

BEFORE INSERT OR UPDATE OF sal, deptno OR DELETE

ON scott.emp

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;

--Example 9–2 INSTEAD OF TRIGGER

CREATE OR REPLACE VIEW order_info

SELECT c.customer_id,

c.cust_last_name,

c.cust_first_name,

o.order_id,

o.order_date,

o.order_status

FROM customers c, orders o

WHERE c.customer_id = o.customer_id;

CREATE OR REPLACE TRIGGER order_info_insert

INSTEAD OF INSERT

ON order_info

DECLARE

duplicate_info EXCEPTION;

PRAGMA EXCEPTION_INIT (duplicate_info, -00001);

BEGIN

INSERT INTO customers (customer_id, cust_last_name, cust_first_name)

VALUES (:new.customer_id, :new.cust_last_name, :new.cust_first_name);

INSERT INTO orders (order_id, order_date, customer_id)

VALUES (:new.order_id, :new.order_date, :new.customer_id);

EXCEPTION

WHEN duplicate_info

THEN

RAISE_APPLICATION_ERROR (num => -20107,

msg => 'Duplicate customer or order ID');

END order_info_insert;

--INSERT INTO order_info VALUES (999, 'Smith', 'John', 2500, TO_DATE('13-MAR-2001', 'DD-MON-YYYY'), 0)

--Example 9–3 INSTEAD OF TRIGGER ON NESTED TABLE COLUMN OF VIEW

-- Create type of nested table element:

CREATE OR REPLACE TYPE nte

AUTHID DEFINER IS OBJECT

(emp_id NUMBER (6),

lastname VARCHAR2 (25),

job VARCHAR2 (10),

sal NUMBER (8, 2));

-- Created type of nested table:

CREATE OR REPLACE TYPE emp_list_ IS TABLE OF nte;

-- Create view:

CREATE OR REPLACE VIEW dept_view

SELECT d.department_id,

d.department_name,

CAST (

MULTISET (SELECT e.employee_id,

e.last_name,

e.job_id,

e.salary

FROM employees e

WHERE e.department_id = d.department_id) AS emp_list_)

emplist

FROM departments d;

-- Create trigger:

CREATE OR REPLACE TRIGGER dept_emplist_tr

INSTEAD OF INSERT

ON NESTED TABLE emplist OF dept_view

REFERENCING NEW AS Employee PARENT AS Department

FOR EACH ROW

BEGIN

-- Insert on nested table translates to insert on base table:

INSERT INTO employees (employee_id,

last_name,

email,

hire_date,

job_id,

salary,

department_id)

VALUES (:Employee.emp_id, -- employee_id

:Employee.lastname, -- last_name

:Employee.lastname || '@company.com', -- email

SYSDATE, -- hire_date

:Employee.job, -- job_id

:Employee.sal, -- salary

:Department.department_id -- department_id

);

END;

INSERT INTO TABLE (SELECT d.emplist

FROM dept_view d

WHERE department_id = 10)

VALUES (1001,

'Glenn',

'AC_MGR',

10000);

SELECT employee_id,

last_name,

job_id,

salary

FROM employees

WHERE department_id = 10;

--Example 9–4 Compound Trigger Logs Changes to One Table in Another Table

CREATE TABLE employee_salaries

(

employee_id NUMBER NOT NULL,

change_date DATE NOT NULL,

salary NUMBER (8, 2) NOT NULL,

CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),

CONSTRAINT fk_employee_salaries FOREIGN KEY

(employee_id)

REFERENCES employees (employee_id) ON DELETE CASCADE

)

CREATE OR REPLACE TRIGGER maintain_employee_salaries FOR

UPDATE OF salary

ON employees

COMPOUND TRIGGER

-- Declarative Part:

-- Choose small threshhold value to show how example works:

threshhold CONSTANT SIMPLE_INTEGER := 7;

TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE

INDEX BY SIMPLE_INTEGER;

salaries salaries_t;

idx SIMPLE_INTEGER := 0;

PROCEDURE flush_array

n CONSTANT SIMPLE_INTEGER := salaries.COUNT ();

BEGIN

FORALL j IN 1 .. n

INSERT INTO employee_salaries

VALUES salaries (j);

salaries.delete ();

idx := 0;

DBMS_OUTPUT.PUT_LINE ('Flushed ' || n || ' rows');

END flush_array;

-- AFTER EACH ROW Section:

AFTER EACH ROW

BEGIN

idx := idx + 1;

salaries (idx).employee_id := :NEW.employee_id;

salaries (idx).change_date := SYSTIMESTAMP;

salaries (idx).salary := :NEW.salary;

IF idx >= threshhold

THEN

flush_array ();

END IF;

END

AFTER EACH ROW;

-- AFTER STATEMENT Section:

AFTER STATEMENT

BEGIN

flush_array ();

END

AFTER STATEMENT;

END maintain_employee_salaries;

--Increase salary OF every employee IN department 50 BY 10%:

UPDATE employees

SET salary = salary * 1.1

WHERE department_id = 50

--Wait two seconds:

BEGIN

DBMS_LOCK.SLEEP (2);

END;

--Increase salary of every employee in department 50 by 5%:

UPDATE employees

SET salary = salary * 1.05

WHERE department_id = 50

--See changes to employees table reflected in employee_salaries table:

SELECT employee_id, COUNT (*) c

FROM employee_salaries

GROUP BY employee_id

--Example 9–5 Compound Trigger Avoids Mutating-Table Error

CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise FOR

UPDATE OF Salary

ON Employees

COMPOUND TRIGGER

Ten_Percent CONSTANT NUMBER := 0.1;

TYPE Salaries_t IS TABLE OF Employees.Salary%TYPE;

Avg_Salaries Salaries_t;

TYPE Department_IDs_t IS TABLE OF Employees.Department_ID%TYPE;

Department_IDs Department_IDs_t;

-- Declare collection type and variable:

TYPE Department_Salaries_t IS TABLE OF Employees.Salary%TYPE

INDEX BY VARCHAR2 (80);

Department_Avg_Salaries Department_Salaries_t;

BEFORE STATEMENT

BEGIN

SELECT AVG (e.Salary), NVL (e.Department_ID, -1)

BULK COLLECT INTO Avg_Salaries, Department_IDs

FROM Employees e

GROUP BY e.Department_ID;

FOR j IN 1 .. Department_IDs.COUNT ()

LOOP

Department_Avg_Salaries (Department_IDs (j)) := Avg_Salaries (j);

END LOOP;

END

BEFORE STATEMENT;

AFTER EACH ROW

BEGIN

IF :NEW.Salary - :Old.Salary >

Ten_Percent * Department_Avg_Salaries (:NEW.Department_ID)

THEN

Raise_Application_Error (-20000, 'Raise too big');

END IF;

END

AFTER EACH ROW;

END Check_Employee_Salary_Raise;

--Note:

--The examples in the following topics use these tables, which share the column Deptno:

CREATE TABLE emp

(

Empno NUMBER NOT NULL,

Ename VARCHAR2 (10),

Job VARCHAR2 (9),

Mgr NUMBER (4),

Hiredate DATE,

Sal NUMBER (7, 2),

Comm NUMBER (7, 2),

Deptno NUMBER (2) NOT NULL

);

CREATE TABLE dept

(

Deptno NUMBER (2) NOT NULL,

Dname VARCHAR2 (14),

Loc VARCHAR2 (13),

Mgr_no NUMBER,

Dept_type NUMBER

);

--Example 9–6 Foreign Key Trigger for Child Table

CREATE OR REPLACE TRIGGER emp_dept_check

BEFORE INSERT OR UPDATE OF Deptno

ON emp

FOR EACH ROW

WHEN (NEW.Deptno IS NOT NULL)

-- Before row is inserted or DEPTNO is updated in emp table,

-- fire this trigger to verify that new foreign key value (DEPTNO)

-- is present in dept table.

DECLARE

Dummy INTEGER; -- Use for cursor fetch

Invalid_department EXCEPTION;

Valid_department EXCEPTION;

Mutating_table EXCEPTION;

PRAGMA EXCEPTION_INIT (Invalid_department, -4093);

PRAGMA EXCEPTION_INIT (Valid_department, -4092);

PRAGMA EXCEPTION_INIT (Mutating_table, -4091);

-- Cursor used to verify parent key value exists.

-- If present, lock parent key's row so it cannot be deleted

-- by another transaction until this transaction is

-- committed or rolled back.

CURSOR Dummy_cursor (Dn NUMBER)

SELECT Deptno

FROM dept

WHERE Deptno = Dn

FOR UPDATE OF Deptno;

BEGIN

OPEN Dummy_cursor (:NEW.Deptno);

FETCH Dummy_cursor INTO Dummy;

-- Verify parent key.

-- If not found, raise user-specified error code and message.

-- If found, close cursor before allowing triggering statement to complete:

IF Dummy_cursor%NOTFOUND

THEN

RAISE Invalid_department;

ELSE

RAISE Valid_department;

END IF;

CLOSE Dummy_cursor;

EXCEPTION

WHEN Invalid_department

THEN

CLOSE Dummy_cursor;

Raise_application_error (

-20000,

'Invalid Department' || ' Number' || TO_CHAR (:NEW.deptno));

WHEN Valid_department

THEN

CLOSE Dummy_cursor;

WHEN Mutating_table

THEN

NULL;

END;

--Example 9–7 UPDATE and DELETE RESTRICT Trigger for Parent Table

CREATE OR REPLACE TRIGGER dept_restrict

BEFORE DELETE OR UPDATE OF Deptno

ON dept

FOR EACH ROW

-- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,

-- check for dependent foreign key values in emp;

-- if any are found, roll back.

DECLARE

Dummy INTEGER; -- Use for cursor fetch

employees_present EXCEPTION;

employees_not_present EXCEPTION;

PRAGMA EXCEPTION_INIT (employees_present, -4094);

PRAGMA EXCEPTION_INIT (employees_not_present, -4095);

-- Cursor used to check for dependent foreign key values.

CURSOR Dummy_cursor (Dn NUMBER)

SELECT Deptno

FROM emp

WHERE Deptno = Dn;

BEGIN

OPEN Dummy_cursor (:OLD.Deptno);

FETCH Dummy_cursor INTO Dummy;

-- If dependent foreign key is found, raise user-specified

-- error code and message. If not found, close cursor

-- before allowing triggering statement to complete.

IF Dummy_cursor%FOUND

THEN

RAISE employees_present; -- Dependent rows exist

ELSE

RAISE employees_not_present; -- No dependent rows exist

END IF;

CLOSE Dummy_cursor;

EXCEPTION

WHEN employees_present

THEN

CLOSE Dummy_cursor;

Raise_application_error (

-20001,

'Employees Present in' || ' Department ' || TO_CHAR (:OLD.DEPTNO));

WHEN employees_not_present

THEN

CLOSE Dummy_cursor;

END;

--Example 9–8 UPDATE AND DELETE SET NULL TRIGGER FOR PARENT TABLE

CREATE OR REPLACE TRIGGER dept_set_null

AFTER DELETE OR UPDATE OF Deptno

ON dept

FOR EACH ROW

-- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,

-- set all corresponding dependent foreign key values in emp to NULL:

BEGIN

IF UPDATING AND :OLD.Deptno != :NEW.Deptno OR DELETING

THEN

UPDATE emp

SET emp.Deptno = NULL

WHERE emp.Deptno = :OLD.Deptno;

END IF;

END;

--Example 9–9 DELETE CASCADE Trigger for Parent Table

CREATE OR REPLACE TRIGGER dept_del_cascade

AFTER DELETE

ON dept

FOR EACH ROW

-- Before row is deleted from dept,

-- delete all rows from emp table whose DEPTNO is same as

-- DEPTNO being deleted from dept table:

BEGIN

DELETE FROM emp

WHERE emp.Deptno = :OLD.Deptno;

END;

--Example 9–10 UPDATE CASCADE Trigger for Parent Table

-- Generate sequence number to be used as flag

-- for determining if update occurred on column:

CREATE SEQUENCE Update_sequence

INCREMENT BY 1

MAXVALUE 5000

CYCLE;

CREATE OR REPLACE PACKAGE Integritypackage

AUTHID DEFINER

Updateseq NUMBER;

END Integritypackage;

CREATE OR REPLACE PACKAGE BODY Integritypackage

END Integritypackage;

-- Create flag col:

ALTER TABLE emp ADD Update_id NUMBER;

CREATE OR REPLACE TRIGGER dept_cascade1

BEFORE UPDATE OF Deptno

ON dept

DECLARE

-- Before updating dept table (this is a statement trigger),

-- generate sequence number

-- & assign it to public variable UPDATESEQ of

-- user-defined package named INTEGRITYPACKAGE:

BEGIN

Integritypackage.Updateseq := Update_sequence.NEXTVAL;

END;

CREATE OR REPLACE TRIGGER dept_cascade2

AFTER DELETE OR UPDATE OF Deptno

ON dept

FOR EACH ROW

-- For each department number in dept that is updated,

-- cascade update to dependent foreign keys in emp table.

-- Cascade update only if child row was not updated by this trigger:

BEGIN

IF UPDATING

THEN

UPDATE emp

SET Deptno = :NEW.Deptno, Update_id = Integritypackage.Updateseq --from 1st

WHERE emp.Deptno = :OLD.Deptno AND Update_id IS NULL;

/* Only NULL if not updated by 3rd trigger

fired by same triggering statement */

END IF;

IF DELETING

THEN

-- After row is deleted from dept,

-- delete all rows from emp table whose DEPTNO is same as

-- DEPTNO being deleted from dept table:

DELETE FROM emp

WHERE emp.Deptno = :OLD.Deptno;

END IF;

END;

CREATE OR REPLACE TRIGGER dept_cascade3

AFTER UPDATE OF Deptno

ON dept

BEGIN

UPDATE emp

SET Update_id = NULL

WHERE Update_id = Integritypackage.Updateseq;

END;

--Note:

--Example 9–11 needs this data structure:

CREATE TABLE Salgrade

(

Grade NUMBER,

Losal NUMBER,

Hisal NUMBER,

Job_classification VARCHAR2 (9)

);

--Example 9–11 Trigger Checks Complex Constraints

CREATE OR REPLACE TRIGGER salary_check

BEFORE INSERT OR UPDATE OF Sal, Job

ON Emp

FOR EACH ROW

DECLARE

Minsal NUMBER;

Maxsal NUMBER;

Salary_out_of_range EXCEPTION;

PRAGMA EXCEPTION_INIT (Salary_out_of_range, -4096);

BEGIN

/* Retrieve minimum & maximum salary for employee's new job classification

from SALGRADE table into MINSAL and MAXSAL: */

SELECT Losal, Hisal

INTO Minsal, Maxsal

FROM Salgrade

WHERE Job_classification = :NEW.Job;

/* If employee's new salary is less than or greater than

job classification's limits, raise exception.

Exception message is returned and pending INSERT or UPDATE statement

that fired the trigger is rolled back: */

IF (:NEW.Sal < Minsal OR :NEW.Sal > Maxsal)

THEN

RAISE Salary_out_of_range;

END IF;

EXCEPTION

WHEN Salary_out_of_range

THEN

Raise_application_error (

-20300,

'Salary '

|| TO_CHAR (:NEW.Sal)

|| ' out of range for '

|| 'job classification '

|| :NEW.Job

|| ' for employee '

|| :NEW.Ename);

WHEN NO_DATA_FOUND

THEN

Raise_application_error (-20322, 'Invalid Job Classification');

END;

--Example 9–12 TRIGGER Enforces Security

CREATE OR REPLACE TRIGGER Emp_permit_changes

BEFORE INSERT OR DELETE OR UPDATE

ON Emp

DECLARE

Dummy INTEGER;

Not_on_weekends EXCEPTION;

Not_on_holidays EXCEPTION;

Non_working_hours EXCEPTION;

PRAGMA EXCEPTION_INIT (Not_on_weekends, -4097);

PRAGMA EXCEPTION_INIT (Not_on_holidays, -4098);

PRAGMA EXCEPTION_INIT (Non_working_hours, -4099);

BEGIN

-- Check for weekends:

IF (TO_CHAR (SYSDATE, 'DAY') = 'SAT' OR TO_CHAR (SYSDATE, 'DAY') = 'SUN')

THEN

RAISE Not_on_weekends;

END IF;

-- Check for company holidays:

SELECT COUNT (*)

INTO Dummy

FROM Company_holidays

WHERE Day = SYSDATE;

IF dummy > 0

THEN

RAISE Not_on_holidays;

END IF;

-- Check for work hours (8am to 6pm):

IF (TO_CHAR (SYSDATE, 'HH24') < 8 OR TO_CHAR (SYSDATE, 'HH24') > 18)

THEN

RAISE Non_working_hours;

END IF;

EXCEPTION

WHEN Not_on_weekends

THEN

Raise_application_error (

-20324,

'Might not change ' || 'employee table during the weekend');

WHEN Not_on_holidays

THEN

Raise_application_error (

-20325,

'Might not change ' || 'employee table during a holiday');

WHEN Non_working_hours

THEN

Raise_application_error (

-20326,

'Might not change ' || 'emp table during nonworking hours');

END;

--Note:

--Example 9–13 needs this change to this data structure:

ALTER TABLE Emp ADD(

Uppername VARCHAR2(20),

Soundexname VARCHAR2(20));

--Example 9–13 Trigger Derives New Column Values

CREATE OR REPLACE TRIGGER Derived

BEFORE INSERT OR UPDATE OF Ename

ON Emp

/* Before updating the ENAME field, derive the values for

the UPPERNAME and SOUNDEXNAME fields. Restrict users

from updating these fields directly: */

FOR EACH ROW

BEGIN

:NEW.Uppername := UPPER (:NEW.Ename);

:NEW.Soundexname := SOUNDEX (:NEW.Ename);

END;

CREATE OR REPLACE TYPE Book_t AS OBJECT

(Booknum NUMBER,

Title VARCHAR2 (20),

Author VARCHAR2 (20),

Available CHAR (1));

CREATE OR REPLACE TYPE Book_list_t AS TABLE OF Book_t;

--THE TABLE Book_table IS created AND populated LIKE this:

DROP TABLE Book_table;

CREATE TABLE Book_table

(

Booknum NUMBER,

Section VARCHAR2 (20),

Title VARCHAR2 (20),

Author VARCHAR2 (20),

Available CHAR (1)

);

INSERT INTO Book_table (Booknum,

Section,

Title,

Author,

Available)

VALUES (121001,

'Classic',

'Iliad',

'Homer',

'Y');

INSERT INTO Book_table (Booknum,

Section,

Title,

Author,

Available)

VALUES (121002,

'Novel',

'Gone with the Wind',

'Mitchell M',

'N');

--SELECT * FROM Book_table ORDER BY Booknum;

DROP TABLE Library_table;

CREATE TABLE Library_table (Section VARCHAR2 (20));

INSERT INTO Library_table (Section)

VALUES ('Novel');

INSERT INTO Library_table (Section)

VALUES ('Classic');

SELECT *

FROM Library_table

ORDER BY Section;

CREATE OR REPLACE VIEW Library_view

SELECT i.Section,

CAST (MULTISET (SELECT b.Booknum,

b.Title,

b.Author,

b.Available

FROM Book_table b

WHERE b.Section = i.Section) AS Book_list_t)

BOOKLIST

FROM Library_table i;

CREATE OR REPLACE TRIGGER Library_trigger

INSTEAD OF INSERT

ON Library_view

FOR EACH ROW

DECLARE

Bookvar Book_t;

I INTEGER;

BEGIN

INSERT INTO Library_table

VALUES (:NEW.Section);

FOR i IN 1 .. :NEW.Booklist.COUNT

LOOP

Bookvar := :NEW.Booklist (i);

INSERT INTO Book_table (Booknum,

Section,

Title,

Author,

Available)

VALUES (Bookvar.booknum,

:NEW.Section,

Bookvar.Title,

Bookvar.Author,

bookvar.Available);

END LOOP;

END;

--Insert a new row into Library_view:

INSERT INTO Library_view (Section, Booklist)

VALUES ('History',

book_list_t (book_t (121330,

'Alexander',

'Mirth',

'Y')));

--See the effect on Library_view:

SELECT *

FROM Library_view

ORDER BY Section;

--Example 9–14 TRIGGER Logs Changes TO EMPLOYEES.SALARY

--CREATE log table:

DROP TABLE Emp_log;

CREATE TABLE Emp_log

(

Emp_id NUMBER,

Log_date DATE,

New_salary NUMBER,

Action VARCHAR2 (20)

);

--CREATE TRIGGER that inserts row in log table after EMPLOYEES.SALARY is updated:

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;

UPDATE EMPLOYEES.SALARY:

UPDATE employees

SET salary = salary + 1000.0

WHERE Department_id = 20;

SELECT * FROM Emp_log;

--Example 9–15 Conditional TRIGGER Prints Salary CHANGE Information

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;

SELECT last_name,

department_id,

salary,

job_id

FROM employees

WHERE department_id IN (10, 20, 90)

ORDER BY department_id, last_name;

--Triggering statement:

UPDATE employees

SET salary = salary * 1.05

WHERE department_id IN (10, 20, 90);

--QUERY:

SELECT salary

FROM employees

WHERE job_id = 'AD_PRES';

--Example 9–16 TRIGGER Modifies CLOB COLUMNS

DROP TABLE tab1;

CREATE TABLE tab1 (c1 CLOB);

INSERT INTO tab1

VALUES ('<h1>HTML Document Fragment</h1><p>Some text.', 3);

CREATE OR REPLACE TRIGGER trg1

BEFORE UPDATE

ON tab1

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE ('Old value of CLOB column: ' || :OLD.c1);

DBMS_OUTPUT.PUT_LINE ('Proposed new value of CLOB column: ' || :NEW.c1);

:NEW.c1 := :NEW.c1 || TO_CLOB ('<hr><p>Standard footer paragraph.');

DBMS_OUTPUT.PUT_LINE ('Final value of CLOB column: ' || :NEW.c1);

END;

SET SERVEROUTPUT ON;

UPDATE tab1

SET c1 = '<h1>Different Document Fragment</h1><p>Different text.';

SELECT * FROM tab1;

--Example 9–17 TRIGGER WITH REFERENCING Clause

CREATE TABLE new

(

field1 NUMBER,

field2 VARCHAR2 (20)

);

CREATE OR REPLACE TRIGGER Print_salary_changes

BEFORE UPDATE

ON new

REFERENCING NEW AS Newest

FOR EACH ROW

BEGIN

:Newest.Field2 := TO_CHAR (:newest.field1);

END;

--Example 9–18 TRIGGER REFERENCES OBJECT_VALUE Pseudocolumn

--CREATE , populate, and show object table:

CREATE OR REPLACE TYPE t

AUTHID DEFINER AS OBJECT

(n NUMBER, m NUMBER)

CREATE TABLE tbl OF t

BEGIN

FOR j IN 1 .. 5

LOOP

INSERT INTO tbl

VALUES (t (j, 0));

END LOOP;

END;

SELECT *

FROM tbl

ORDER BY n;

CREATE TABLE tbl_history

(

d DATE,

old_obj t,

new_obj t

)

CREATE OR REPLACE TRIGGER Tbl_Trg

AFTER UPDATE

ON tbl

FOR EACH ROW

BEGIN

INSERT INTO tbl_history (d, old_obj, new_obj)

VALUES (SYSDATE, :OLD.OBJECT_VALUE, :NEW.OBJECT_VALUE);

END Tbl_Trg;

--Update object table:

UPDATE tbl

SET tbl.n = tbl.n + 1

--Show old and new values:

BEGIN

FOR j IN (SELECT d, old_obj, new_obj FROM tbl_history)

LOOP

DBMS_OUTPUT.PUT_LINE (

j.d

|| ' -- old: '

|| j.old_obj.n

|| ' '

|| j.old_obj.m

|| ' -- new: '

|| j.new_obj.n

|| ' '

|| j.new_obj.m);

END LOOP;

END;

--SCHEMA Triggers

--Example 9–19 BEFORE STATEMENT TRIGGER ON SAMPLE SCHEMA HR

CREATE OR REPLACE TRIGGER drop_trigger

BEFORE DROP

ON hr.SCHEMA

BEGIN

RAISE_APPLICATION_ERROR (num => -20000, msg => 'Cannot drop object');

END;

--DATABASE Triggers

--Example 9–20 AFTER STATEMENT TRIGGER ON DATABASE

CREATE TRIGGER log_errors

AFTER SERVERERROR

ON DATABASE

BEGIN

IF (IS_SERVERERROR (1017))

THEN

NULL; -- (substitute code that processes logon error)

ELSE

NULL; -- (substitute code that logs error code)

END IF;

END;

--Example 9–21 TRIGGER Monitors Logons

CREATE OR REPLACE TRIGGER check_user

AFTER LOGON

ON DATABASE

BEGIN

check_user;

EXCEPTION

WHEN OTHERS

THEN

RAISE_APPLICATION_ERROR (

-20000,

'Unexpected error: ' || DBMS_UTILITY.Format_Error_Stack);

END;

--INSTEAD OF CREATE Triggers

--Example 9–22 INSTEAD OF CREATE TRIGGER ON SCHEMA

CREATE OR REPLACE TRIGGER t

INSTEAD OF CREATE

ON SCHEMA

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)';

END;

--Example 9–23 Trigger Invokes Java Subprogram

CREATE OR REPLACE PROCEDURE Before_delete (Id IN NUMBER, Ename VARCHAR2)

LANGUAGE JAVA

NAME 'thjvTriggers.beforeDelete (oracle.sql.NUMBER, oracle.sql.CHAR)';

CREATE OR REPLACE TRIGGER Pre_del_trigger

BEFORE DELETE

ON Tab

FOR EACH ROW

CALL Before_delete (:OLD.Id, :OLD.Ename)

--Example 9–24 TRIGGER Cannot Handle EXCEPTION IF Remote DATABASE IS Unavailable

CREATE OR REPLACE TRIGGER employees_tr

AFTER INSERT

ON employees

FOR EACH ROW

BEGIN

-- When remote database is unavailable, compilation fails here:

INSERT INTO employees@remote (employee_id,

first_name,

last_name,

email,

hire_date,

job_id)

VALUES (99,

'Jane',

'Doe',

'jane.doe@example.com',

SYSDATE,

'ST_MAN');

EXCEPTION

WHEN OTHERS

THEN

INSERT INTO emp_log (Emp_id,

Log_date,

New_salary,

Action)

VALUES (99,

SYSDATE,

NULL,

'Could not insert');

RAISE;

END;

--Example 9–25 Workaround FOR Example 9–24

CREATE OR REPLACE PROCEDURE insert_row_proc

AUTHID CURRENT_USER

no_remote_db EXCEPTION; -- declare exception

PRAGMA EXCEPTION_INIT (no_remote_db, -20000);

BEGIN

INSERT INTO employees@remote (employee_id,

first_name,

last_name,

email,

hire_date,

job_id)

VALUES (99,

'Jane',

'Doe',

'jane.doe@example.com',

SYSDATE,

'ST_MAN');

EXCEPTION

WHEN OTHERS

THEN

INSERT INTO emp_log (Emp_id,

Log_date,

New_salary,

Action)

VALUES (99,

SYSDATE,

NULL,

'Could not insert row.');

RAISE_APPLICATION_ERROR (-20000, 'Remote database is unavailable.');

END;

CREATE OR REPLACE TRIGGER employees_tr

AFTER INSERT

ON employees

FOR EACH ROW

BEGIN

insert_row_proc;

END;

--Example 9–26 TRIGGER Causes Mutating-TABLE ERROR

-- Create log table

DROP TABLE LOG;

CREATE TABLE LOG

(

emp_id NUMBER (6),

l_name VARCHAR2 (25),

f_name VARCHAR2 (20)

);

-- Create trigger that updates log and then reads employees

CREATE OR REPLACE TRIGGER log_deletions

AFTER DELETE

ON employees

FOR EACH ROW

DECLARE

n INTEGER;

BEGIN

INSERT INTO LOG

VALUES (:OLD.employee_id, :OLD.last_name, :OLD.first_name);

SELECT COUNT (*) INTO n FROM employees;

DBMS_OUTPUT.PUT_LINE ('There are now ' || n || ' employees.');

END;

-- Issue triggering statement:

DELETE FROM employees

WHERE employee_id = 197;

DELETE FROM employees

WHERE employee_id = 197;

--Example 9–27 Update Cascade

DROP TABLE p;

CREATE TABLE p (p1 NUMBER CONSTRAINT pk_p_p1 PRIMARY KEY);

INSERT INTO p

VALUES (1);

INSERT INTO p

VALUES (2);

INSERT INTO p

VALUES (3);

DROP TABLE f;

CREATE TABLE f (f1 NUMBER CONSTRAINT fk_f_f1 REFERENCES p);

INSERT INTO f

VALUES (1);

INSERT INTO f

VALUES (2);

INSERT INTO f

VALUES (3);

CREATE TRIGGER pt

AFTER UPDATE

ON p

FOR EACH ROW

BEGIN

UPDATE f

SET f1 = :NEW.p1

WHERE f1 = :OLD.p1;

END;

--QUERY:

SELECT *

FROM p

ORDER BY p1;

--QUERY:

SELECT *

FROM f

ORDER BY f1;

--Issue triggering STATEMENT:

UPDATE p

SET p1 = p1 + 1;

--QUERY:

SELECT *

FROM p

ORDER BY p1;

--QUERY:

SELECT *

FROM f

ORDER BY f1;

--Example 9–28 Viewing Information About TRIGGERS

CREATE OR REPLACE TRIGGER Emp_count

AFTER DELETE

ON employees

DECLARE

n INTEGER;

BEGIN

SELECT COUNT (*) INTO n FROM employees;

DBMS_OUTPUT.PUT_LINE ('There are now ' || n || ' employees.');

END;

COLUMN Trigger_type FORMAT A15

COLUMN Triggering_event FORMAT A16

COLUMN Table_name FORMAT A11

COLUMN Trigger_body FORMAT A50

--QUERY:

SELECT Trigger_type, Triggering_event, Table_name

FROM USER_TRIGGERS

WHERE Trigger_name = 'EMP_COUNT';

--QUERY:

SELECT Trigger_body

FROM USER_TRIGGERS

WHERE Trigger_name = 'EMP_COUNT';

ORACLE PROJECTS

MENUS

Thursday, 10 July 2014

PL/SQL Triggers