INSTEAD OF trigger (FOR EACH ROW)

[femina]

i have a problem in understanding the concept which i read ill jus give the snippet code

create trigger WORKER_LODG_MANAG_update
instead of UPDATE on  WORKER_LODG_MANAG 
for each row
begin
   if :old.name<>:new.name
     then 
        update WORKER 
                     set name=:new.name
                     where name=:old.name;
    end if;
   if :old.lodging<>:new.lodging 
    then 
        update WORKER 
                     set lodging=:new.lodging 
                     where name=:old.name;
    end if;
   if :old.manager<>:new.manager
     then 
        update LODGING  
                     set manager=:new.manager
                     where lodging=:old.lodging;
    end if;
 end;

then if i have a command UPDATE WORKER set lodging='bangalore' where name='bart'; which would cause INSTEAD OF trigger to get exectued

now i dont understand why in the INSTEAD OF trigger i have a comparison condition for name if :old.name<>:new.name then ..... end if; because i have to check for names thats bart only
please help me know the reason or let me know if i have to make the question still more clearer

[amitpatel66]

Why you need to update a value with a same value which already exist in the table??

If the name stored in the worked table is = 'AAAA' and you try to do an update of name filed to value 'AAAA' then do you think this update makes sense really?
The IF condition is just to check that the field to be updated does not have the value similar to the new value to be updated to.

I hope this clears your doubt!!

[femina]

sir but
say i have three records with names ramya jyothi and bart.
now i have to make a change in only bart. because my update is
update worker set location='bangalore' where name='bart';
so i should not change the first two record names ramya and jyothi
this particular doubt iam not clear with.please try to help me

Why you need to update a value with a same value which already exist in the table??

If the name stored in the worked table is = 'AAAA' and you try to do an update of name filed to value 'AAAA' then do you think this update makes sense really?
The IF condition is just to check that the field to be updated does not have the value similar to the new value to be updated to.

I hope this clears your doubt!!

[femina]

sir in UPDATE WORKER set lodging='bangalore' where name='bart'
now please tell me old lodging is one present in the table already
new lodging is banaglore.
now like this i have old name as bart which is the present in the table again.
can i have a new name.because name is in the where condition
example in INSERT there is no old value ,in DELETE there is no new value

sir but
say i have three records with names ramya jyothi and bart.
now i have to make a change in only bart. because my update is
update worker set location='bangalore' where name='bart';
so i should not change the first two record names ramya and jyothi
this particular doubt iam not clear with.please try to help me

[debasisdas]

what exactly you are trying to validate using the trigger ?

[amitpatel66]

sir in UPDATE WORKER set lodging='bangalore' where name='bart'
now please tell me old lodging is one present in the table already
new lodging is banaglore.
now like this i have old name as bart which is the present in the table again.
can i have a new name.because name is in the where condition
example in INSERT there is no old value ,in DELETE there is no new value

I think you are confused with INSTEAD OF TRIGGER.
The instead of trigger is written on a view and not on a table and when you try to update a view, the INSTEAD OF TRIGGER fires. When you update a table directly, then this trigger will not fire.

INSTEAD OF TRIGGERS are mainly used to overcome the problem of refrential integrity and do an update/insert in to the base table directly instead of updating a view.

[femina]

update WORKER set lodging='BANAGLORE' where name='BART';
now this update command is replaced by the instead of trigger
now please help me with the reason
in the trigger that i have mentioned (first question)
will the first if gets exectued

if :old.name<>:new.name
  then update WORKER
      set name=:new.name
      where name=:old.name 
 end if;

i want the location to be changed for name BART.
but the if condition is confusing me. is there any :new.name when name is used in WHERE condition

what exactly you are trying to validate using the trigger ?

[femina]

ok so if the query is like
update WORKER_LODGING_MANAGER set lodging='BANGALORE' where name='BART'; then what happens for the
if condition

if :old.name<>:new.name
  then update WORKER
      set name=:new.name
      where name=:old.name 
 end if;

kindly excuse me for the continuous queries

update WORKER set lodging='BANAGLORE' where name='BART';
now this update command is replaced by the instead of trigger
now please help me with the reason
in the trigger that i have mentioned (first question)
will the first if gets exectued

if :old.name<>:new.name
  then update WORKER
      set name=:new.name
      where name=:old.name 
 end if;

i want the location to be changed for name BART.
but the if condition is confusing me. is there any :new.name when name is used in WHERE condition

[amitpatel66]

ok so if the query is like
update WORKER_LODGING_MANAGER set lodging='BANGALORE' where name='BART'; then what happens for the
if condition

if :old.name<>:new.name
  then update WORKER
      set name=:new.name
      where name=:old.name 
 end if;

kindly excuse me for the continuous queries

Well, when you run this update statement, it will check for second IF condition checking for :new.lodging <> :old.lodging and if they are not equal then it updates the lodging table for all employees whose name = 'BART'

[debasisdas]

What exactly is this WORKER_LODGING_MANAGER .

[femina]

WORKER_LODGING_MANAGER is the view created from WORKER and LODGING tables

What exactly is this WORKER_LODGING_MANAGER .

[debasisdas]

WORKER_LODGING_MANAGER is the view created from WORKER and LODGING tables

Then what is this WORKER_LODG_MANAG on which the trigger is based.

[femina]

let me give my question clearly again

 CREATE TRIGGER WORKER_LODG_MANAG_update
instead OF UPDATE ON  WORKER_LODG_MANAG 
FOR each ROW
BEGIN
   IF :old.name<>:NEW.name
     THEN 
        UPDATE WORKER 
                     SET name=:NEW.name
                     WHERE name=:old.name;
    END IF;
   IF :old.lodging<>:NEW.lodging 
    THEN 
        UPDATE WORKER 
                     SET lodging=:NEW.lodging 
                     WHERE name=:old.name;
    END IF;
   IF :old.manager<>:NEW.manager
     THEN 
        UPDATE LODGING  
                     SET manager=:NEW.manager
                     WHERE lodging=:old.lodging;
    END IF;
 END;

now WORKER_LODG_MANAG is a view created from worker and lodging tables
to change BART s lodging i can use UPDATE WORKER set lodging='BANGALORE' where name='BART' directly on the WORKER table
or use an INSTEAD OF trigger and update via WORKER_LODG_MANAG view like UPDATE WORKER_LODG_MANAG set lodging='BANGALORE' where name='BART';
but the first if condition in INSTEAD OF trigger is confusing

IF :old.name<>:NEW.name
     THEN 
        UPDATE WORKER 
                     SET name=:NEW.name
                     WHERE name=:old.name;
    END IF;

IN THE TRIGGER--- FOR EACH ROW--- MEANS FOR ALL ROWS OF VIEW OR ONLY FOR THE CHANGED ROWS .i want to change only rows that have BART as the name
kindly excuse me for the lengthy question

[femina]

in INSTEAD OF trigger for a view say i use it for update statement
iam confused with FOR EACH ROW specifies
all rows in view
or
only the changed rows(that gets affected by update on the view)

[amitpatel66]

Threads merged for better management of forum

MODERATOR

[amitpatel66]

let me give my question clearly again

 CREATE TRIGGER WORKER_LODG_MANAG_update
instead OF UPDATE ON  WORKER_LODG_MANAG 
FOR each ROW
BEGIN
   IF :old.name<>:NEW.name
     THEN 
        UPDATE WORKER 
                     SET name=:NEW.name
                     WHERE name=:old.name;
    END IF;
   IF :old.lodging<>:NEW.lodging 
    THEN 
        UPDATE WORKER 
                     SET lodging=:NEW.lodging 
                     WHERE name=:old.name;
    END IF;
   IF :old.manager<>:NEW.manager
     THEN 
        UPDATE LODGING  
                     SET manager=:NEW.manager
                     WHERE lodging=:old.lodging;
    END IF;
 END;

now WORKER_LODG_MANAG is a view created from worker and lodging tables
to change BART s lodging i can use UPDATE WORKER set lodging='BANGALORE' where name='BART' directly on the WORKER table
or use an INSTEAD OF trigger and update via WORKER_LODG_MANAG view like UPDATE WORKER_LODG_MANAG set lodging='BANGALORE' where name='BART';
but the first if condition in INSTEAD OF trigger is confusing

IF :old.name<>:NEW.name
     THEN 
        UPDATE WORKER 
                     SET name=:NEW.name
                     WHERE name=:old.name;
    END IF;

IN THE TRIGGER--- FOR EACH ROW--- MEANS FOR ALL ROWS OF VIEW OR ONLY FOR THE CHANGED ROWS .i want to change only rows that have BART as the name
kindly excuse me for the lengthy question

The IF condition for name will fail becuase you will get the records only for name = 'BART' and :old.name and "new.name both will be equal to 'BART'

[amitpatel66]

Check this out. I have emp_det table,dept table and I create a view emp_dept and also INSTEAD of TRIGGER on my view similar to your case:

 
SQL> create view emp_dept AS SELECT e.empno,d.deptno,d.deptname FROM emp_det e,dept22 d where e.dept = d.deptno AND e.empno = d.empno;
 
View created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  CREATE OR REPLACE TRIGGER
  2  temp_io INSTEAD OF UPDATE ON emp_dept FOR EACH ROW
  3  BEGIN
  4  IF :old.empno <> :new.empno THEN
  5  DBMS_OUTPUT.PUT_LINE(:new.empno||','||:old.empno);
  6  UPDATE emp_det set empno = :new.empno WHERE empno = :old.empno;
  7  END IF;
  8  IF :old.deptname <> :new.deptname THEN
  9  DBMS_OUTPUT.PUT_LINE(:new.deptname||','||:old.deptname);
 10  UPDATE dept22 SET deptname = :new.deptname WHERE empno =  :old.empno;
 11  END IF;
 12* END;
SQL> /
 
Trigger created.
 
SQL> update emp_dept SET deptname = 'FIRST' WHERE empno = 1;
FIRST,Welcome
 
1 row updated.
 
SQL> ed
Wrote file afiedt.buf
 
  1* update emp_dept SET empno = 1 WHERE empno = 1
SQL> /
 
1 row updated.
 
 

Is the above example clear?
The first IF condition in my trigger fails so it does not print the old and new empno as shown in the code above.

[femina]

thanks a lot sir
i tried ur example and it worked propely
thanks

Check this out. I have emp_det table,dept table and I create a view emp_dept and also INSTEAD of TRIGGER on my view similar to your case:

 
SQL> create view emp_dept AS SELECT e.empno,d.deptno,d.deptname FROM emp_det e,dept22 d where e.dept = d.deptno AND e.empno = d.empno;
 
View created.
 
SQL> ed
Wrote file afiedt.buf
 
  1  CREATE OR REPLACE TRIGGER
  2  temp_io INSTEAD OF UPDATE ON emp_dept FOR EACH ROW
  3  BEGIN
  4  IF :old.empno <> :new.empno THEN
  5  DBMS_OUTPUT.PUT_LINE(:new.empno||','||:old.empno);
  6  UPDATE emp_det set empno = :new.empno WHERE empno = :old.empno;
  7  END IF;
  8  IF :old.deptname <> :new.deptname THEN
  9  DBMS_OUTPUT.PUT_LINE(:new.deptname||','||:old.deptname);
 10  UPDATE dept22 SET deptname = :new.deptname WHERE empno =  :old.empno;
 11  END IF;
 12* END;
SQL> /
 
Trigger created.
 
SQL> update emp_dept SET deptname = 'FIRST' WHERE empno = 1;
FIRST,Welcome
 
1 row updated.
 
SQL> ed
Wrote file afiedt.buf
 
  1* update emp_dept SET empno = 1 WHERE empno = 1
SQL> /
 
1 row updated.
 
 

Is the above example clear?
The first IF condition in my trigger fails so it does not print the old and new empno as shown in the code above.

[amitpatel66]

You are welcome :)

MODERATOR