[ORAID!] - HOWTO:Insert on View

ORAID! | Oracle | How-To | Tips & Trick | Links

Sent by : Holly

HOWTO:Insert on View
INSTEAD OF Trigger Pada view dapat dilakukan Updating/delete/insert, tidak hanya read saja.
Contoh :
Pada contoh berikut, data customer disimpan dalam 2 tabel.
View ALL_CUSTOMERS merupakan gabungan dari table CUSTOMERS_SJ dan CUSTOMERS_PA. Trigger INSTEAD OF digunakan untuk memasukkan record.

CREATE TABLE customers_sj (
cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2) );

CREATE TABLE customers_pa (
cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2) );

CREATE TYPE customer_t AS OBJECT (
cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2),
location VARCHAR2(20) );

CREATE VIEW all_customers (cust) AS
SELECT customer_t (cust, address, credit, 'SAN_JOSE')
FROM customers_sj
UNION ALL
SELECT customer_t (cust, address, credit, 'PALO_ALTO')
FROM customers_pa;

CREATE TRIGGER instrig INSTEAD OF
INSERT ON all_customers
FOR EACH ROW
BEGIN
IF (:new.cust.location = 'SAN_JOSE') THEN
INSERT INTO customers_sj
VALUES (:new.cust.cust, :new.cust.address,:new.cust.credit);
ELSE
INSERT INTO customers_pa
VALUES (:new.cust.cust, :new.cust.address, :new.cust.credit);
END IF;
END;

Ini dapat pula digunakan pada saat UPDATE/DELETE.

About SyahID