TRIGGERS:
Trigger defines an
action the database should take when some database-related event occurs. Triggers may be used to supplement
integrity constraints, enforce complex business rules, or to audit changes to
the database.
Triggers are executed
automatically and transparently to the user when the given event occurs. Some of the common commands that can
cause a trigger to execute are INSERT, UPDATE, and DELETE. Triggers should be used to enforce rules
that CANNOT be enforced through referential integrity.
*New as of Oracle8i triggers can now be executed on system level events Startup, Shutdown, SQLError and user event login and logout. Also triggers can now be written for DDL commands Create, Alter, and Drop
Valid Trigger
Types:
BEFORE INSERT row
BEFORE INSERT statement
AFTER INSERT row
AFTER INSERT statement
BEFORE UPDATE row
BEFORE UPDATE statement
AFTER UPDATE row
AFTER UPDATE statement
BEFORE DELETE row
BEFORE DELETE statement
AFTER DELETE row
AFTER DELETE statement
*INSTEAD OF row On a view, allows an indirect way of updating the underlying tables.
*INSTEAD OF statement On a view, allows an indirect way of updating the underlying tables.
STARTUP triggers fire when the database is opened by an instance. Their attributes include the system event, instance number, and database name.SHUTDOWN triggers fire just before the server starts shutting down an instance. You can use these triggers to make subscribing applications shut down completely when the database shuts down. (For abnormal instance shutdown these triggers may not be fired.) The attributes of SHUTDOWN triggers include the system event, instance number, and database name.
SERVERERROR triggers fire when a specified error occurs, or when any error occurs if no error number is specified. Their attributes include the system event and error number.
*LOGON triggers fire after a successful logon of a user.
*LOGOFF triggers fire at the start of a user logoff.
*BEFORE CREATE and AFTER CREATE triggers fire when a schema object is created in the database or schema.
*BEFORE ALTER and AFTER ALTER triggers fire when a schema object is altered in the database or schema.
*BEFORE DROP and AFTER DROP triggers fire when a schema object is dropped from the database or schema.
* New as of Version 8i.
Here is what Oracle has to say about triggers
ORDER OF TRIGGER
FIRING:
1.
Before Statement
2. Before Row
3. Statement
4. After Row
5. After Statement
With Triggers Oracle gives
you two globals records.
1. NEW: This is an image of what the row will look
like AFTER the statement is executed.
2. OLD: This is an image of the row
BEFORE the statement is executed.
BEFORE|AFTER
INSERT|DELETE|UPDATE
ON
table_name
[FOR EACH ROW]
[WHEN (condition)]
DECLARE
Variable declarations;
BEGIN
Statements;
EXCEPTION
WHEN (exception_error) THEN
END;
Example:
/* debit_balance: retrieve customer
balance.
@author: rtimlin
@original:
06-Mar-2001
@updated:
Table:
@version: 1.0
**** Modification History ****
Date
User Description
===================================================
3/6/01 rtimlin
Original.
3/8/01
flast Added
Exception handler.
*/
BEFORE UPDATE ON
customer
FOR
EACH ROW WHEN (new.balance > new.credit_limit)
DECLARE
e_too_much EXCEPTION;
BEGIN
INSERT INTO debit_balance_report (action_dt, customer_number)
VALUES (SYSDATE, :new.customer_number);
-- If the Amount over the credit limit is greater than 10%, Then Raise an
Exception
IF ((:new.balance - :new.credit_limit)/:new.credit_limit > .1)
THEN
RAISE e_too_much;
END IF;
EXCEPTION
WHEN e_too_much THEN
RAISE_APPLICATION_ERROR (-20001, 'Over Credit Limit Amount is TOO
High');
END;
/
All your PL/SQL functions, procedures and triggers must have an exception handler or you will receive one full letter grade deduction and your work will be returned to be fixed.
Note the indentation
above, failure to indent for readability will result in one full letter grade
deduction and your work being returned to you to fix.
All your PL/SQL functions, procedures and triggers must have comments similar to the above or you will receive one full letter grade deduction and your work will be returned to be fixed.
The
RAISE_APPLICATION_ERROR prcoedure takes two input parameters: The error number (which must be between
–20001 and –20999) and the error message to be displayed.
SQL> UPDATE customer SET
balance = 1101 WHERE customer_number = 124;
SQL> select trigger_name, table_name from user_triggers; -- Information on triggers;
CREATE OR REPLACE TRIGGER tg_all_customer
BEFORE UPDATE OR INSERT OR DELETE ON customer FOR EACH ROW
DECLARE
v_customer_nr customer.customer_number%TYPE;
e_null_pkey EXCEPTION;
e_change_pkey EXCEPTION;
BEGIN
IF (INSERTING) THEN
IF (:NEW.customer_number IS NULL) THEN
SELECT customer_seq.NextVal INTO v_customer_nr FROM dual;
:NEW.customer_number := v_customer_nr;
END IF;
:NEW.last_update := SYSDATE;
:NEW.user_name := USER;
ELSIF UPDATING THEN
IF (:NEW.customer_number IS NULL) THEN
RAISE e_null_pkey;
END IF;
IF (:NEW.customer_number != :OLD.customer_number) THEN
RAISE e_change_pkey;
END IF;
:NEW.last_update := SYSDATE;
:NEW.user_name := USER;
INSERT INTO customer_history (customer_number, lname, fname, street, city, state, zip_code,
credit_limit, balance, last_update, user_name, action)
VALUES (:OLD.customer_number, :OLD.lname, :OLD.fname, :OLD.street, :OLD.city, :OLD.state,
:OLD.zip_code, :OLD.credit_limit, :OLD.balance, SYSDATE, USER, 'UPDATE');
ELSIF DELETING THEN
INSERT INTO customer_history (customer_number, lname, fname, street, city, state, zip_code,
credit_limit, balance, last_update, user_name, action)
VALUES (:OLD.customer_number, :OLD.lname, :OLD.fname, :OLD.street, :OLD.city, :OLD.state,
:OLD.zip_code, :OLD.credit_limit, :OLD.balance, SYSDATE, USER, 'DELETE');
END IF;
EXCEPTION
WHEN e_null_pkey THEN
RAISE_APPLICATION_ERROR (-20001, 'Cannot make pkey null');
WHEN e_change_pkey THEN
RAISE_APPLICATION_ERROR (-20002, 'Cannot change pkey');
END;
/
The following example shows an INSTEAD OF trigger for updating rows in the manager_info view, which lists all the departments and their managers.
Let dept be a relational table containing a list of departments,
CREATE TABLE dept ( deptno NUMBER PRIMARY KEY, deptname VARCHAR2(20), manager_num NUMBER );
Let emp be a relational table containing the list of employees and the departments in which they work.
CREATE TABLE emp ( empno NUMBER PRIMARY KEY, empname VARCHAR2(20), deptno NUMBER REFERENCES dept(deptno), startdate DATE ); ALTER TABLE dept ADD (FOREIGN KEY(manager_num) REFERENCES emp(empno));
Create the manager_info view that lists all the managers for each department:
CREATE VIEW manager_info AS SELECT d.deptno, d.deptname, e.empno, e.empname FROM emp e, dept d WHERE e.empno = d.manager_num;
Now, define an INSTEAD-OF trigger to handle the inserts on the view. An insert into the manager_info view can be translated into an update to the manager_num column of the dept table.
In the trigger, you can also enforce the constraint that there must be at least one employee working in the department for a person to be a manager of that department.
CREATE OR REPLACE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
REFERENCING NEW AS n -- new manager information
FOR EACH ROW
DECLARE
empCount NUMBER;
BEGIN
/* First check to make sure that the number of employees
* in the department is greater than one */
SELECT COUNT(*) INTO empCount
FROM emp e
WHERE e.deptno = :n.deptno;
/* If there are enough employees then make him or her the manager */
IF empCount >= 1 THEN
UPDATE dept d
SET manager_num = :n.empno
WHERE d.deptno = :n.deptno;
END IF;
END;
/
Any inserts to the manager_info view, such as:
INSERT INTO manager_info VALUES (200,'Sports',1002,'Jack');
VERSION 2:
CREATE OR REPLACE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
REFERENCING NEW AS n -- new manager information
FOR EACH ROW
DECLARE
empCount NUMBER;
BEGIN
/* First check to make sure that the number of employees
* in the department is greater than one */
SELECT COUNT(*) INTO empCount
FROM emp e
WHERE e.empno = :n.empno;
/* If there are enough employees then make him or her the manager */
IF empCount < 1 THEN
INSERT INTO emp (empno, empname, deptno, startdate)
VALUES (:n.empno, :n.empname, null, SYSDATE);
ELSE
UPDATE emp SET empname = :n.empname WHERE empno = :n.empno;
END IF;
UPDATE dept SET manager_num = :n.empno, deptname= :n.deptname WHERE deptno = :n.deptno;
IF SQL%ROWCOUNT <= 0 THEN
INSERT INTO dept (deptno, deptname, manager_num)
VALUES (:n.deptno, :n.deptname, :n.empno);
END IF;
UPDATE emp SET deptno = :n.deptno WHERE empno = :n.empno;
END;
/
will fire the manager_info_insert trigger and update the underlying tables. Similar triggers can specify appropriate actions for INSERT and DELETE on the view.
The INSTEAD OF option to the CREATE TRIGGER statement can only be used for triggers created over views. The BEFORE and AFTER options cannot be used for triggers created over views.
The CHECK option for views is not enforced when inserts or updates to the view are done using INSTEAD OF triggers. The INSTEAD OF trigger body must enforce the check.
Home work #2:
Using the tg_all_customer trigger as an example, create similar triggers for sales_rep, orders, part, and order_line. Order_line doesn't need to have the primary key set for it since it is derived from part and order. However you should still set the last_update and user_name columns as well as inserting into the history table.
1. Create last_update and user_name columns for each table.
2. Create history tables for each of the tables with
the same definition as the original tables. No constraints on these
tables.
CREATE TABLE customer_history AS
SELECT
* FROM customer WHERE 1=2;
3. ALTER TABLE customer_history ADD (action VARCHAR2(20));
DUE: 6-Jul-2004
Turn In:
1. Alter Table commands to add user_name and last_update to each table.
2. Create Table command to create history tables.
3. Source code for the triggers.
4. Some SQL Update, Insert, Delete commands to show that they all work. Select * From table... after each statement.