19.4. INSTEAD OF Triggers
INSTEAD OF triggers
control insert, update, and delete operations on views, not tables. They can be used to make nonupdateable views updateable and to override the default behavior of views that are updateable.
19.4.1. Creating an INSTEAD OF Trigger
To create (or replace) an INSTEAD OF trigger, use the syntax shown here:
1 CREATE [OR REPLACE] TRIGGER trigger_name
2 INTEAD OF operation
3 ON view name
4 FOR EACH ROW
5 BEGIN
6 ... code goes here ...
7 END;
The table contains an explanation of this code:
Line(s) | Description |
|---|
1 | States that a trigger is to be created with the unique name supplied. Specifying OR REPLACE is optional. If the trigger exists, and REPLACE is not specified, then your attempt to create the trigger anew will result in an ORA-4081 error. | 2 | This is where we see differences between INSTEAD OF triggers and other types of triggers. Because INSTEAD OF triggers aren't really triggered by an event, we don't need to specify AFTER or BEFORE or provide an event name. What we do specify is the operation that the trigger is to fire in place of (or instead of). Stating INSTEAD OF followed by one of INSERT, UPDATE, or DELETE accomplishes this. | 3 | This line is somewhat like the corresponding line for DDL and database event triggers in that the keyword ON is specified. The similarities end there: instead of specifying DATABASE or SCHEMA, we provide the name of the view to which the trigger is to apply. | 4-7 | Contains standard PL/SQL code. |
INSTEAD OF triggers are best explained with an example. Let's use one of my favorite topics: pizza delivery! Before we can start pounding the dough, we have to put a system in place to monitor our deliveries. We will need three tables: one to track actual deliveries, one to track delivery areas, and one to track our massive fleet of drivers (remember the first rule of businessalways think big!).
/* File on web: pizza_tables.sql */
CREATE TABLE delivery
(delivery_id NUMBER,
delivery_start DATE,
delivery_end DATE,
area_id NUMBER,
driver_id NUMBER);
CREATE TABLE area
(area_id NUMBER, area_desc VARCHAR2(30));
CREATE TABLE driver
(driver_id NUMBER, driver_name VARCHAR2(30));
For the sake of brevity I will not create any primary or foreign keys.
We will also need three sequences to provide unique identifiers for our tables.
CREATE SEQUENCE delivery_id_seq;
CREATE SEQUENCE area_id_seq;
CREATE SEQUENCE driver_id_seq;
To avoid having to explain relational database design and normalization to our employees, we will simplify deliveries into a single view displaying delivery, area, and driver information:
CREATE OR REPLACE VIEW delivery_info AS
SELECT d.delivery_id,
d.delivery_start,
d.delivery_end,
a.area_desc,
dr.driver_name
FROM delivery d,
area a,
driver dr
WHERE a.area_id = d.area_id
AND dr.driver_id = d.driver_id;
Because our system relies heavily on this view for query functionality, why not make it available for insert, update, and delete as well? We cannot directly issues DML statements against the view; it is a join of multiple tables. How would Oracle know what to do with an INSERT
? In fact, we need to tell Oracle very explicitly what to do when an insert, update, or delete operation occurs against the delivery_info view; in other words, we need to tell it what to do instead of trying to insert, update, or delete. Thus, we will use INSTEAD OF
triggers. Let's start with the INSERT trigger.
19.4.2. The INSTEAD OF INSERT Trigger
Our INSERT trigger will perform four basic operations:
Ensure that the delivery_end value is NULL. All delivery completions must be done via an update. Try to find the driver ID based on the name provided. If the name cannot be found, then assign a new ID and create a driver entry using the name and the new ID. Try to find the area ID based on the name provided. If the name cannot be found, then assign a new ID and create an area entry using the name and the new ID. Create an entry in the delivery table.
Bear in mind that this example is intended to demonstrate triggersnot how to effectively build a business system! After a while we will probably wind up with a multitude of duplicate driver and area entries. However, using this view speeds things up by not requiring drivers and areas to be predefined, and in the fast-paced world of pizza delivery, time is money!
/* File on web: pizza_triggers.sql */
CREATE OR REPLACE TRIGGER delivery_info_insert
INSTEAD OF INSERT
ON delivery_info
DECLARE
-- cursor to get the driver ID by name
CURSOR curs_get_driver_id (cp_driver_name VARCHAR2)
IS
SELECT driver_id
FROM driver
WHERE driver_name = cp_driver_name;
v_driver_id NUMBER;
-- cursor to get the area ID by name
CURSOR curs_get_area_id (cp_area_desc VARCHAR2)
IS
SELECT area_id
FROM area
WHERE area_desc = cp_area_desc;
v_area_id NUMBER;
BEGIN
/*
|| Make sure the delivery_end value is NULL
*/
IF :NEW.delivery_end IS NOT NULL
THEN
raise_application_error
(-20000
, 'Delivery end date value must be NULL when delivery created'
);
END IF;
/*
|| Try to get the driver ID using the name. If not found
|| then create a brand new driver ID from the sequence
*/
OPEN curs_get_driver_id (UPPER (:NEW.driver_name));
FETCH curs_get_driver_id
INTO v_driver_id;
IF curs_get_driver_id%NOTFOUND
THEN
SELECT driver_id_seq.NEXTVAL
INTO v_driver_id
FROM DUAL;
INSERT INTO driver
(driver_id, driver_name
)
VALUES (v_driver_id, UPPER (:NEW.driver_name)
);
END IF;
CLOSE curs_get_driver_id;
/*
|| Try to get the area ID using the name. If not found
|| then create a brand new area ID from the sequence
*/
OPEN curs_get_area_id (UPPER (:NEW.area_desc));
FETCH curs_get_area_id
INTO v_area_id;
IF curs_get_area_id%NOTFOUND
THEN
SELECT area_id_seq.NEXTVAL
INTO v_area_id
FROM DUAL;
INSERT INTO area
(area_id, area_desc
)
VALUES (v_area_id, UPPER (:NEW.area_desc)
);
END IF;
CLOSE curs_get_area_id;
/*
|| Create the delivery entry
*/
INSERT INTO delivery
(delivery_id, delivery_start
, delivery_end, area_id, driver_id
)
VALUES (delivery_id_seq.NEXTVAL, NVL (:NEW.delivery_start, SYSDATE)
, NULL, v_area_id, v_driver_id
);
END;
19.4.3. The INSTEAD OF UPDATE Trigger
Now let's move on to the UPDATE trigger. For the sake of simplicity, we will only allow updating of the delivery_end field, and only if it is NULL to start with. We can't have drivers resetting delivery times.
CREATE OR REPLACE TRIGGER delivery_info_update
INSTEAD OF UPDATE
ON delivery_info
DECLARE
-- cursor to get the delivery entry
CURSOR curs_get_delivery (cp_delivery_id NUMBER)
IS
SELECT delivery_end
FROM delivery
WHERE delivery_id = cp_delivery_id
FOR UPDATE OF delivery_end;
v_delivery_end DATE;
BEGIN
OPEN curs_get_delivery (:NEW.delivery_id);
FETCH curs_get_delivery INTO v_delivery_end;
IF v_delivery_end IS NOT NULL
THEN
RAISE_APPLICATION_ERROR (
-20000, 'The delivery end date has already been set');
ELSE
UPDATE delivery
SET delivery_end = :NEW.delivery_end
WHERE CURRENT OF curs_get_delivery;
END IF;
CLOSE curs_get_delivery;
END;
19.4.4. The INSTEAD OF DELETE Trigger
The DELETE trigger is the simplest of all. It merely ensures that we are not deleting a completed entry and then removes the delivery record. The driver and area records remain intact.
CREATE OR REPLACE TRIGGER delivery_info_delete
INSTEAD OF DELETE
ON delivery_info
BEGIN
IF :OLD.delivery_end IS NOT NULL
THEN
RAISE_APPLICATION_ERROR (
-20000,'Completed deliveries cannot be deleted');
END IF;
DELETE delivery
WHERE delivery_id = :OLD.delivery_id;
END;
19.4.5. Populating the Tables
Now, with a single INSERT focused on the delivery information we know (the driver and the area), all of the required tables are populated:
SQL> INSERT INTO delivery_info(delivery_id,
2 delivery_start,
3 delivery_end,
4 area_desc,
5 driver_name)
6 VALUES
7 (NULL, NULL, NULL, 'LOCAL COLLEGE', 'BIG TED');
1 row created.
SQL> SELECT * FROM delivery;
DELIVERY_ID DELIVERY_ DELIVERY_ AREA_ID DRIVER_ID
----------- --------- --------- ---------- ----------
1 13-JAN-02 1 1
SQL> SELECT * FROM area;
AREA_ID AREA_DESC
---------- ------------------------------
1 LOCAL COLLEGE
SQL> SELECT * FROM driver;
DRIVER_ID DRIVER_NAME
---------- ------------------------------
1 BIG TED
19.4.6. INSTEAD OF Triggers on Nested Tables
Oracle has introduced many ways to store complex data structures as columns in tables or views. This is logically effective because the linkage between a table or view and its columns is obvious. Technically, it can require some not-so-obvious trickery to allow even the simplest of operations, like inserting records into these complex structures. One of these complex situations can be resolved with a special type of INSTEAD OF trigger, as shown below.
Consider the following view joining the chapters of a book with the lines in the chapter:
CREATE OR REPLACE VIEW book_chapter_view AS
SELECT chapter_number,
chapter_title,
CAST(MULTISET(SELECT *
FROM book_line
WHERE chapter_number = book_chapter.chapter_number)
AS book_line_t) lines
FROM book_chapter;
I agree that the view is far too obtuse for its purpose (why not just join the tables directly?), but it easily demonstrates the use of INSTEAD OF triggers on nested table columns-or on any object or collection column in a view.
After creating a record in the BOOK_CHAPTER table and querying the view, I'll see the following, which explains that there are no lines in the chapter yet:
CHAPTER_NUMBER CHAPTER_TITLE
-------------- ------------------------------
LINES(CHAPTER_NUMBER, LINE_NUMBER, LINE_TEXT)
---------------------------------------------
18 Triggers
BOOK_LINE_T( )
So I then try to create the first line to get past my writer's block:
SQL> INSERT INTO TABLE(SELECT lines
2 FROM book_chapter_view
3 WHERE chapter_number = 18)
4 VALUES(18,1,'Triggers are...');
INSERT INTO TABLE(SELECT lines
*
ERROR at line 1:
ORA-25015: cannot perform DML on this nested table view column
Apparently, Oracle has determined that there is not enough information available to just insert values into the BOOK_LINE table masquerading as the LINES column in the view. Thus, an INSTEAD OF trigger is required to make the intent crystal clear.
CREATE OR REPLACE TRIGGER lines_ins
INSTEAD OF INSERT ON NESTED TABLE lines OF book_chapter_view
BEGIN
INSERT INTO book_line
(chapter_number,
line_number,
line_text)
VALUES(:PARENT.chapter_number,
:NEW.line_number,
:NEW.line_text);
END;
Now I can add the first line:
SQL> INSERT INTO TABLE ( SELECT lines
2 FROM book_chapter_view
3 WHERE chapter_number = 18 )
4 VALUES(18,1,'Triggers Are...');
1 row created.
SQL> SELECT *
2 FROM book_chapter_view;
CHAPTER_NUMBER CHAPTER_TITLE
-------------- ------------------------------
LINES(CHAPTER_NUMBER, LINE_NUMBER, LINE_TEXT)
-----------------------------------------------------
18 Triggers
BOOK_LINE_T(BOOK_LINE_O(18, 1, 'Triggers Are...'))
Note that the SQL used to create the trigger is just like what is used for other INSTEAD OF triggers except for two things:
| 
No comments:
Post a Comment