INSTEAD OF trigger (FOR EACH ROW)

[femina]

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

[CODE=oracle]create trigger WORKER_LODG_MAN AG_update
instead of UPDATE on WORKER_LODG_MAN AG
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.lo dging
where name=:old.name;
end if;
if :old.manager<>: new.manager
then
update LODGING
set manager=:new.ma nager
where lodging=:old.lo dging;
end if;
end;[/CODE]

then if i have a command UPDATE WORKER set lodging='bangal ore' 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='banga lore' 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='bangal ore' 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='banga lore' 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='bangal ore' 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='BANAGL ORE' 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='BANGAL ORE' 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='BANAGL ORE' 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='BANGAL ORE' 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_MAN AG on which the trigger is based.

[femina]

new discussion

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_MAN AG is a view created from worker and lodging tables
to change BART s lodging i can use UPDATE WORKER set lodging='BANGAL ORE' where name='BART' directly on the WORKER table
or use an INSTEAD OF trigger and update via WORKER_LODG_MAN AG view like UPDATE WORKER_LODG_MAN AG set lodging='BANGAL ORE' 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]

For Each Row

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