We help IT Professionals succeed at work.

Update views and instead of triggers

d27m11y
d27m11y asked
on

Can I update a view which is defined on aggregate function using "instead of trigger", please advise
Comment
Watch Question

Some more details please... it's not quite clear what you're trying to achieve
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes, it's possible in general,  but the specifics will depend on exactly what you're doing, and how the trigger is implemented

Author

Commented:
Please find my code below for INSTEAD OF TRIGGER. When I issue the following update statement

update outemp
set totalsal = 5000
where job ='CLERK';

it is failing, what is the right way of doing it., Kindly advise
/* Formatted on 2011/12/07 14:38 (Formatter Plus v4.8.8) */
CREATE OR REPLACE  VIEW scott.outemp 
AS
   SELECT sum(sal) as totalsal, job, deptno from emp
   group by  job, deptno
   order by deptno;

CREATE OR REPLACE TRIGGER IOT_OUTEMP
INSTEAD OF UPDATE ON OUTEMP
BEGIN
UPDATE SCOTT.OUTEMP
SET TOTALSAL = :NEW.TOTALSAL
WHERE jOB = :NEW.JOB;
END;
/

Open in new window

No. Triggers cannot be placed on views, since view rows cannot be updated, inserted, or deleted directly.
The reason that what you are trying to do doesn't make sense is that "TotalSal" is a sum of the "sal" column in the Emp table. Also, because Outtemp is a view, all the data in that view is derived from the Emp table, and cannot be modified directly.

You cannot update a sum, you have to update the values that make up the sum to give you the desired result.
Most Valuable Expert 2011
Top Expert 2012

Commented:
your trigger doesn't make much sense.


first, you're trying to update the view whenever the view is updated.  The trigger should be updating the underlying tables.

Also, even if were to update the tables.  given what the view represents,  how could that functionality work?

Each employee has his or her own salary,  what would you expect a change of the departmental salary total
to mean for any of the employees in that department?
Most Valuable Expert 2011
Top Expert 2012

Commented:
AngryBinary,

"Triggers cannot be placed on views"

this is not true.  "instead of triggers"   can, as of 8i be placed on views.

also , "view rows cannot be updated, inserted, or deleted directly."  isn't true in general either,  for this particular view, yes.
but key-preserved views, may be updatable.

Author

Commented:

@sdstuber

I am not sure of what you said. Does it mean, we code "instead of triggers" to make changes in the underlying tables of the view.

Can you please guide me with an example?

Author

Commented:

I would like to understand the concept of "instead of triggers" using which we can update the views where views are defined using aggregate functions. Kindly advise with an example
Most Valuable Expert 2011
Top Expert 2012
Commented:
using your original example, try this...

the trigger will evenly distribute changes in a department/job salary total among all employees in that department/job.

That is,  if you double the totalsal,  each employees will get his/her salary doubled.
If the total salary is multiplied by .9,  each employee will have a 10% salary deduction

if a division by 0 error would occur, then the salary is set to NULL


CREATE OR REPLACE TRIGGER iot_outemp
    INSTEAD OF UPDATE
    ON outemp
DECLARE
    v_factor   NUMBER;
BEGIN
    v_factor  := :new.totalsal / NULLIF(:old.totalsal, 0);

    UPDATE emp
       SET sal  = sal * v_factor
     WHERE job = :new.job AND deptno = :new.deptno;
END;

Author

Commented:
tHIS IS ABSOLUTELY WONDERFULE, THANKS SO MUCH!