469,343 Members | 5,350 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,343 developers. It's quick & easy.

INSTEAD OF trigger (FOR EACH ROW)

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

Expand|Select|Wrap|Line Numbers
  1. create trigger WORKER_LODG_MANAG_update
  2. instead of UPDATE on  WORKER_LODG_MANAG 
  3. for each row
  4. begin
  5.    if :old.name<>:new.name
  6.      then 
  7.         update WORKER 
  8.                      set name=:new.name
  9.                      where name=:old.name;
  10.     end if;
  11.    if :old.lodging<>:new.lodging 
  12.     then 
  13.         update WORKER 
  14.                      set lodging=:new.lodging 
  15.                      where name=:old.name;
  16.     end if;
  17.    if :old.manager<>:new.manager
  18.      then 
  19.         update LODGING  
  20.                      set manager=:new.manager
  21.                      where lodging=:old.lodging;
  22.     end if;
  23.  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
Dec 14 '07 #1
18 3835
amitpatel66
2,367 Expert 2GB
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!!
Dec 14 '07 #2
femina
35
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!!
Dec 14 '07 #3
femina
35
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
Dec 14 '07 #4
debasisdas
8,127 Expert 4TB
what exactly you are trying to validate using the trigger ?
Dec 14 '07 #5
amitpatel66
2,367 Expert 2GB
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.
Dec 14 '07 #6
femina
35
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
Expand|Select|Wrap|Line Numbers
  1. if :old.name<>:new.name
  2.   then update WORKER
  3.       set name=:new.name
  4.       where name=:old.name 
  5.  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 ?
Dec 14 '07 #7
femina
35
ok so if the query is like
update WORKER_LODGING_MANAGER set lodging='BANGALORE' where name='BART'; then what happens for the
if condition
Expand|Select|Wrap|Line Numbers
  1. if :old.name<>:new.name
  2.   then update WORKER
  3.       set name=:new.name
  4.       where name=:old.name 
  5.  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
Expand|Select|Wrap|Line Numbers
  1. if :old.name<>:new.name
  2.   then update WORKER
  3.       set name=:new.name
  4.       where name=:old.name 
  5.  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
Dec 14 '07 #8
amitpatel66
2,367 Expert 2GB
ok so if the query is like
update WORKER_LODGING_MANAGER set lodging='BANGALORE' where name='BART'; then what happens for the
if condition
Expand|Select|Wrap|Line Numbers
  1. if :old.name<>:new.name
  2.   then update WORKER
  3.       set name=:new.name
  4.       where name=:old.name 
  5.  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'
Dec 14 '07 #9
debasisdas
8,127 Expert 4TB
What exactly is this WORKER_LODGING_MANAGER .
Dec 14 '07 #10
femina
35
WORKER_LODGING_MANAGER is the view created from WORKER and LODGING tables
What exactly is this WORKER_LODGING_MANAGER .
Dec 14 '07 #11
debasisdas
8,127 Expert 4TB
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.
Dec 14 '07 #12
femina
35
let me give my question clearly again
Expand|Select|Wrap|Line Numbers
  1.  CREATE TRIGGER WORKER_LODG_MANAG_update
  2. instead OF UPDATE ON  WORKER_LODG_MANAG 
  3. FOR each ROW
  4. BEGIN
  5.    IF :old.name<>:NEW.name
  6.      THEN 
  7.         UPDATE WORKER 
  8.                      SET name=:NEW.name
  9.                      WHERE name=:old.name;
  10.     END IF;
  11.    IF :old.lodging<>:NEW.lodging 
  12.     THEN 
  13.         UPDATE WORKER 
  14.                      SET lodging=:NEW.lodging 
  15.                      WHERE name=:old.name;
  16.     END IF;
  17.    IF :old.manager<>:NEW.manager
  18.      THEN 
  19.         UPDATE LODGING  
  20.                      SET manager=:NEW.manager
  21.                      WHERE lodging=:old.lodging;
  22.     END IF;
  23.  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
Expand|Select|Wrap|Line Numbers
  1. IF :old.name<>:NEW.name
  2.      THEN 
  3.         UPDATE WORKER 
  4.                      SET name=:NEW.name
  5.                      WHERE name=:old.name;
  6.     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
Dec 14 '07 #13
femina
35
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)
Dec 14 '07 #14
amitpatel66
2,367 Expert 2GB
Threads merged for better management of forum

MODERATOR
Dec 14 '07 #15
amitpatel66
2,367 Expert 2GB
let me give my question clearly again
Expand|Select|Wrap|Line Numbers
  1.  CREATE TRIGGER WORKER_LODG_MANAG_update
  2. instead OF UPDATE ON  WORKER_LODG_MANAG 
  3. FOR each ROW
  4. BEGIN
  5.    IF :old.name<>:NEW.name
  6.      THEN 
  7.         UPDATE WORKER 
  8.                      SET name=:NEW.name
  9.                      WHERE name=:old.name;
  10.     END IF;
  11.    IF :old.lodging<>:NEW.lodging 
  12.     THEN 
  13.         UPDATE WORKER 
  14.                      SET lodging=:NEW.lodging 
  15.                      WHERE name=:old.name;
  16.     END IF;
  17.    IF :old.manager<>:NEW.manager
  18.      THEN 
  19.         UPDATE LODGING  
  20.                      SET manager=:NEW.manager
  21.                      WHERE lodging=:old.lodging;
  22.     END IF;
  23.  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
Expand|Select|Wrap|Line Numbers
  1. IF :old.name<>:NEW.name
  2.      THEN 
  3.         UPDATE WORKER 
  4.                      SET name=:NEW.name
  5.                      WHERE name=:old.name;
  6.     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'
Dec 14 '07 #16
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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;
  3.  
  4. View created.
  5.  
  6. SQL> ed
  7. Wrote file afiedt.buf
  8.  
  9.   1  CREATE OR REPLACE TRIGGER
  10.   2  temp_io INSTEAD OF UPDATE ON emp_dept FOR EACH ROW
  11.   3  BEGIN
  12.   4  IF :old.empno <> :new.empno THEN
  13.   5  DBMS_OUTPUT.PUT_LINE(:new.empno||','||:old.empno);
  14.   6  UPDATE emp_det set empno = :new.empno WHERE empno = :old.empno;
  15.   7  END IF;
  16.   8  IF :old.deptname <> :new.deptname THEN
  17.   9  DBMS_OUTPUT.PUT_LINE(:new.deptname||','||:old.deptname);
  18.  10  UPDATE dept22 SET deptname = :new.deptname WHERE empno =  :old.empno;
  19.  11  END IF;
  20.  12* END;
  21. SQL> /
  22.  
  23. Trigger created.
  24.  
  25. SQL> update emp_dept SET deptname = 'FIRST' WHERE empno = 1;
  26. FIRST,Welcome
  27.  
  28. 1 row updated.
  29.  
  30. SQL> ed
  31. Wrote file afiedt.buf
  32.  
  33.   1* update emp_dept SET empno = 1 WHERE empno = 1
  34. SQL> /
  35.  
  36. 1 row updated.
  37.  
  38.  
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.
Dec 14 '07 #17
femina
35
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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;
  3.  
  4. View created.
  5.  
  6. SQL> ed
  7. Wrote file afiedt.buf
  8.  
  9.   1  CREATE OR REPLACE TRIGGER
  10.   2  temp_io INSTEAD OF UPDATE ON emp_dept FOR EACH ROW
  11.   3  BEGIN
  12.   4  IF :old.empno <> :new.empno THEN
  13.   5  DBMS_OUTPUT.PUT_LINE(:new.empno||','||:old.empno);
  14.   6  UPDATE emp_det set empno = :new.empno WHERE empno = :old.empno;
  15.   7  END IF;
  16.   8  IF :old.deptname <> :new.deptname THEN
  17.   9  DBMS_OUTPUT.PUT_LINE(:new.deptname||','||:old.deptname);
  18.  10  UPDATE dept22 SET deptname = :new.deptname WHERE empno =  :old.empno;
  19.  11  END IF;
  20.  12* END;
  21. SQL> /
  22.  
  23. Trigger created.
  24.  
  25. SQL> update emp_dept SET deptname = 'FIRST' WHERE empno = 1;
  26. FIRST,Welcome
  27.  
  28. 1 row updated.
  29.  
  30. SQL> ed
  31. Wrote file afiedt.buf
  32.  
  33.   1* update emp_dept SET empno = 1 WHERE empno = 1
  34. SQL> /
  35.  
  36. 1 row updated.
  37.  
  38.  
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.
Dec 14 '07 #18
amitpatel66
2,367 Expert 2GB
You are welcome :)

MODERATOR
Dec 14 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Dunc | last post: by
8 posts views Thread by Jason | last post: by
8 posts views Thread by joe | last post: by
6 posts views Thread by Scott CM | last post: by
1 post views Thread by Alex | last post: by
2 posts views Thread by Alex | last post: by
5 posts views Thread by William of Ockham | last post: by
reply views Thread by JohnO | last post: by
5 posts views Thread by Chris | last post: by
2 posts views Thread by lenygold via DBMonster.com | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.