Systems Engineering and RDBMS

Updatable views and instead of trigger

Posted by decipherinfosys on April 7, 2009

We have covered different topics pertaining to views and triggers on our blog.  You can search for view and/or trigger on our blog and can find out how to debug triggers, disable/enable triggers, refresh view implications etc.  We are going to talk about updatable views in today’s post i.e. how to update the data in the schema through a view.  In general views can be categorized in two categories:

  • Simple views: created based on one or more table with simple filter criteria without any sorting or grouping operations.
  • Complex views: usually involves aggregate functions, subqueries, group by, order by or distinct clauses.

If we have single table simple views, we can update them directly since the engine can easily identify the records that need to be updated because it is a 1:1 co-relation between the view and the underlying table.  When we have more complex views involving joins, aggregate functions etc., the engine cannot physically identify the record(s) in the underlying tables that need to be updated.  In order to still update the underlying data through those views, both Oracle and SQL Server provide trigger construct ‘instead of trigger’ to be created on such views, which makes such complex views updatable. Instead of triggers are created just like normal triggers.  It is called instead of triggers because instead of firing triggering statements, database fires trigger itself.  Let us first create a table, populate it with data and create view on it which is not updatable.

CREATE TABLE PRODUCT
(
PRODUCT_ID    NUMBER(9) NOT NULL,
PRODUCT_NAME    VARCHAR2(100) NOT NULL,
STYLE        VARCHAR2(10),
COLOR        VARCHAR2(10),
FLAMMABLE    CHAR(1),
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID)
)
/

INSERT INTO PRODUCT(PRODUCT_ID,PRODUCT_NAME,STYLE,COLOR,FLAMMABLE)
VALUES(1,’CANDLES’,’REGULAR’,’BLUE’,’Y’);
INSERT INTO PRODUCT(PRODUCT_ID,PRODUCT_NAME,STYLE,COLOR,FLAMMABLE)
VALUES(2,’FRAMES’,’GOTHIC’,’SILVER’,’N’);
INSERT INTO PRODUCT(PRODUCT_ID,PRODUCT_NAME,STYLE,COLOR,FLAMMABLE)
VALUES(3,’GLASSES’,’MODERN’,’RED’,’N’);
INSERT INTO PRODUCT(PRODUCT_ID,PRODUCT_NAME,STYLE,COLOR,FLAMMABLE)
VALUES(4,’DISHES’,’PLAIN’,’WHITE’,’N’);

Now we will create a view, which unpivots the data. Basically we will represent row values in the columns. In one of our previous blog post we have covered how we can pivot and unpivot the data in Oracle 11g. We have also covered and compared it for both Oracle and SQLServer in one of our whitepaper.

CREATE OR REPLACE VIEW VW_PRODUCT
AS
SELECT PRODUCT_ID,’STYLE’ AS ATTRIBUTE,STYLE vALUE
FROM PRODUCT
UNION ALL
SELECT PRODUCT_ID,’COLOR’,COLOR
FROM PRODUCT
UNION ALL
SELECT PRODUCT_ID,’FLAMMABLE’,FLAMMABLE
FROM PRODUCT;

Now try to update this view directly.

SQL> UPDATE VW_PRODUCT
2  SET VALUE = ‘N’
3  WHERE PRODUCT_ID = 1
4  AND ATTRIBUTE = ‘FLAMMABLE’;
UPDATE VW_PRODUCT
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

Now let us create instead of trigger on this view so that we can update underlying base table.

CREATE OR REPLACE TRIGGER IOF_VW_PRODUCT
INSTEAD OF UPDATE ON VW_PRODUCT
FOR EACH ROW
DECLARE
BEGIN
/* Update for Flammable*/
IF :new.attribute = ‘FLAMMABLE’ THEN
UPDATE PRODUCT
SET FLAMMABLE = :new.value
WHERE PRODUCT_ID = :new.product_id;
END IF;

/* Update for Style*/
IF :new.attribute = ‘STYLE’ THEN
UPDATE PRODUCT
SET STYLE = :new.value
WHERE PRODUCT_ID = :new.product_id;
END IF;

/* Update for COLOR*/
IF :new.attribute = ‘COLOR’ THEN
UPDATE PRODUCT
SET COLOR = :new.value
WHERE PRODUCT_ID = :new.product_id;
END IF;

END IOF_VW_PRODUCT;
/

Once trigger is created, we can issue the update statement.

SQL> UPDATE VW_PRODUCT
2     SET VALUE = ‘N’
3   WHERE PRODUCT_ID = 1
4     AND ATTRIBUTE = ‘FLAMMABLE’;

1 row updated.

Commit the changes and review the updated record.

SQL> select * from product where product_id = 1;

PRODUCT_ID PRODUCT_NA STYLE      COLOR      F
———- ———- ———- ———- –
1 CANDLES    REGULAR    BLUE       N

Flammable value is updated to ‘N’ successfully. Similarly we can also update color of product ‘Frames’ from silver to green without any problems.

SQL> UPDATE VW_PRODUCT
2     SET VALUE = ‘GREEN’
3   WHERE PRODUCT_ID = 2
4     AND ATTRIBUTE = ‘COLOR’;

1 row updated.

Here are the updated values.

SQL> select * from product where product_id = 2;

PRODUCT_ID PRODUCT_NA STYLE      COLOR      F
———- ———- ———- ———- –
2 FRAMES     GOTHIC     GREEN      N

We have shown instead of trigger only for update. We can create it for insert and delete statements as well. It allows us to write normal DML statements against view and works transparently in background.

Resources:

  • Oracle 11g: PL/SQL reference – here.

One Response to “Updatable views and instead of trigger”

  1. […] Updatable views and instead of trigger […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: