| NON-UPDATABLE Views contain any: |
|---|
- One or more Table Joins.
- A
GROUP BY clause.
START WITH ...CONNECT BY.DISTINCT.- Aggregate function (i.e. SUM, AVG, MIN, MAX, COUNT).
- Set operators.
- Derived columns (i.e. concatenated, pseudocolumns, etc).
|
INSTEAD OF triggers are created on
- (usually) a non-updatable view
- a nested table column of a non-updatable view
What?
INSTEAD OF trigger cannot be conditional.- Is the only way to update a view that is not inherently updatable
- Is always a row-level trigger
- Can read
:OLD and :NEW, but cannot change them.
- The timing events (
BEFORE and AFTER) is not applicable for INSTEAD OF triggers.
INSTEAD OF trigger can fire for all three DML statements (I/U/D).- Prior to Oracle8.1.6 INSTEAD OF triggers functionality was included in the Enterprise Edition only.
Why use?
- Used to intercept DML statements and replace them with alternative code.
- Useful when application does not need (or should not) see the underlying tables (i.e. for security reasons). In this case:
- The application issues DMLs against the view
- The INSTEAD OF trigger intercepts and rewrite the DMLs to the underlying tables.
- Useful also when the system uses object-relational functionality.
- Updates against complex views may result in "ORA-01776-cannot modify more than one base table through a join view" or "ORA-01779-cannot modify a column which maps to a non key-preserved table"
How?
- The database fires the
INSTEAD OF trigger instead of running the triggering DML statement.
- The trigger should: (a) determine what operation was intended and (b) perform the appropriate DML operations on the underlying tables.
Creation Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF {dml_statement }
ON {object_name | database | schema}
FOR EACH ROW
[DISABLE]
[WHEN (logical_expression)]
[DECLARE]
declaration_statements;
BEGIN
execution_statements;
END [trigger_name];
/
In the example below (taken from here) you:
(1) Create the base tables
Custormer and
Orders.
(2) Create a
view order_info.
(3) Create a
INSTEAD OF trigger
order_info_insert that translates the DML statement into statements acting on the base tables (Orders and Customers).
(4) Insert data on the
order_info view. Check the insertion on the base tables.
(1-2) Create base tables
Customer,
Orders and view.
SQL> CREATE TABLE customers (
customer_id NUMBER (6) NOT NULL PRIMARY KEY,
cust_first_name VARCHAR2 (20) NOT NULL ,
cust_last_name VARCHAR2 (20) NOT NULL ,
cust_address VARCHAR2 (200) ,
phone_numbers VARCHAR2 (12) ,
nls_language VARCHAR2 (3) ,
nls_territory VARCHAR2 (30) ,
credit_limit NUMBER (9,2) ,
cust_email VARCHAR2 (30) ,
account_mgr_id NUMBER (6));
Table created.
SQL> CREATE TABLE orders (
order_id NUMBER (12) NOT NULL Primary key,
order_date TIMESTAMP NOT NULL ,
order_mode VARCHAR2 (8) ,
customer_id NUMBER (6) NOT NULL ,
order_status NUMBER (2) ,
order_total NUMBER (8,2) ,
sales_rep_id NUMBER (6) ,
promotion_id NUMBER (6));
Table created.
SQL> CREATE OR REPLACE VIEW order_info AS
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;
View created.
SQL>
Now, if you try to perform an INSERT in the view order_info, it will fail with an ORA-01779 error:
SQL>INSERT INTO order_info
values (345, 'Rogers', 'John', 1250, sysdate, 1);
Error: ORA-01779: "cannot modify a column which maps to a non key-preserved table"
*Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
*Action: Modify the underlying base tables directly.
A
Key preserved table means that the row from the base table will appear
AT MOST ONCE in the output view on that table. Here one customer clearly may have multiple orders, so
customer_id will most probably appear multiple times on order_info.
So, if you issue an
INSERT against
order_info, how can the database decide what to do with it?
Should it insert into
orders AND customers? Only into
orders? Or only into
customers?
Well, you need to write an
instead of trigger to intercept the DML against the trigger and issue DMLs against the base table(s) according to your business rules..
In this example, the trigger will
split the INSERT coming from the application and insert on each table accordingly:
(3) Create a
INSTEAD OF trigger
order_info_insert that translates the DML statement into statements acting on the base tables (Orders and Customers).
SQL> 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, order_status)
VALUES (:new.order_id, :new.order_date, :new.customer_id, :new.order_status);
EXCEPTION
WHEN duplicate_info THEN
raise_application_error (
num=> -20107,
msg=> 'Duplicate customer or order ID');
END order_info_insert;
/
Trigger created.
SQL>(4) Insert data on the
order_info view. Check the insertion on the base tables.
SQL> INSERT into order_info
(Customer_id, cust_last_name, cust_first_name, order_id, order_date, order_status)
VALUES
(1, 'Smith', 'John', 2500, '13-AUG-09', 0);
1 rows inserted.
SQL> Select Customer_id, cust_last_name, cust_first_name from customers;
CUSTOMER_ID CUST_FIRST_NAME CUST_LAST_NAME
------------ ---------------- -----------------
1 John Smith
SQL> Select order_id, order_date, order_status from orders;
ORDER_ID ORDER_DATE ORDER_STATUS
---------- ---------------------------------------- ------------------------
2500 13-AUG-09 12.00.00.000000 AM 0
SQL>
| INSTEAD OF triggers on Nested Table Column of View |
Nested Table Column of View (?)
You can create a view that has a
nested table as one of its columns.
Now
if you want to be able to update such a view, you need a instead of trigger.
It will intercept the object-relational
INSERT and translate it to one that inserts into the base table.
(1) To create a view with a nested table as one of its columns you can:
(a) create the object type for the elements of the nested table. In this case, the type
nte captures attributes of employees.
CREATE OR REPLACE TYPE nte AUTHID DEFINER IS
OBJECT (
emp_id number(6),
lastname varchar2(25),
job varchar2(10),
sal number(8,2));
(b) create the object table type. Here it will define a table of objects of the type
nte.
CREATE OR REPLACE TYPE emp_list_ IS TABLE OF nte;
(c) Now you define the view containing the nested table in one of its columns (
emplist).
CREATE OR REPLACE VIEW dept_view AS
SELECT d.department_id,
d.department_name,
CAST (MULTISET (SELECT e.employee_id, e.last_name, e.job_id, i.salary
FROM employees e
WHERE e.department_id = d.department_id)
AS emp_list_
) emplist
FROM departments d;
(d) You can select the contents of emplist, and compare with a direct select of the base table:
SQL> select emplist from dept_view where department_id=10;
EMPLIST(EMP_ID, LASTNAME, JOB, SAL)
-----------------------------------------------
EMP_LIST_(NTE(200, 'Whalen', 'AD_ASST', 4400)
SQL> SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE department_id = 10;
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
200 Whalen AD_ASST 4400
(e) Now, if you try to insert directly into the dept_view, you'll receive an ORA-25015.
SQL> INSERT into table (
select d.emplist
from dept_view d
where department_id = 10
)
VALUES (1001, 'Glenn', 'AC_MGR', 10000);
SQL Error: ORA-25015: cannot perform DML on this nested table view column
*Cause: DML cannot be performed on a nested table view column except through
an INSTEAD OF trigger
*Action: Create an INSTEAD OF trigger over the nested table view column
and then perform the DML.
(f) So you need an INSTEAD OF 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
-- Intercept the insert into the nested table.
-- Translate it to an insert into the base table.
INSERT INTO employees (
employee_id, last_name, email, hire_date, job_id, salary, department_id)
VALUES (
:Employee.emp_id,
:Employee.lastname,
:Employee..lastname || '@company.com',
SYSDATE,
:Employee.job,
:Employee.sal,
:Department.department_id);
END;
(g) Now you if repeat the insert statement, the trigger will fire and perform the insert in the base table employees:
SQL> INSERT into table (
select d.emplist
from dept_view d
where department_id = 10
)
VALUES (1001, 'Glenn', 'AC_MGR', 10000);
1 rows inserted.
(h) You can check the results by querying the
dept_view view and
employees table:
SQL> select emplist from dept_view where department_id = 10;
EMPLIST(EMP_ID, LASTNAME, JOB, SAL)
--------------------------------------------------------------------------------
EMP_LIST_(NTE(200, 'Whalen', 'AD_ASST', 4400), NTE(1001, 'Glenn', 'AC_MGR', 10000))
SQL> SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE department_id = 10;
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
200 Whalen AD_ASST 4400
1001 Glenn AC_MGR 10000
2 rows selected.
