473,326 Members | 2,168 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 4189
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

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

Similar topics

1
by: Dunc | last post by:
I'm new to Postgres, and getting nowhere with a PL/Perl trigger that I'm trying to write - hopefully, someone can give me some insight into what I'm doing wrong. My trigger is designed to reformat...
8
by: Jason | last post by:
I have a table that matches up Securities and Exchanges. Individual securities can belong on multiple exchanges. One of the columns, named PrimaryExchangeFlag, indicates if a particular exchange is...
8
by: joe | last post by:
hi i am trying to write a insted of insert trigger to create a unique id when i insert a record in my database. can anyone give me an example with out using identity. thanks
6
by: Scott CM | last post by:
I have a multi-part question regarding trigger performance. First of all, is there performance gain from issuing the following within a trigger: SELECT PrimaryKeyColumn FROM INSERTED opposed...
1
by: Alex | last post by:
Hi, I need to create a trigger that will trap the insert commands on a table and if the row already exists, it updates the information. I started with this exemple but im getting syntax...
2
by: Alex | last post by:
Hi, I need to create a trigger that will trap the insert commands on a table and if the row already exists, it updates the information. I started with this exemple but im getting syntax...
5
by: William of Ockham | last post by:
Hi, I was asked to recreate a new clean database for our developers because the current one they use is not entirely up to date. So I created a new database and I run into the followin strange...
0
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers automagically. Hope someone finds this as useful as...
5
by: Chris | last post by:
I'm attempting to get Instead Of triggers working. My platform in Solaris, DB2 verison 8.1.x (not sure which). If I create two simple tables with 1 row each: create table test_cc_1 (col1...
2
by: lenygold via DBMonster.com | last post by:
Hi Everebody: I have a table: CREATE TABLE CROSS_REFERENCE (ROW# INTEGER NOT NULL ,KEY_WORD CHAR(16) NOT NULL ,QUERY_DESCR VARCHAR(330) NOT NULL ,PRIMARY KEY (ROW#,KEY_WORD)); It is a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.