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
18 4189
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!!
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!!
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
what exactly you are trying to validate using the trigger ?
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.
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 ?
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
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'
What exactly is this WORKER_LODGING_MANAGER .
WORKER_LODGING_MANAGER is the view created from WORKER and LODGING tables
What exactly is this WORKER_LODGING_MANAGER .
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.
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
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)
Threads merged for better management of forum
MODERATOR
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'
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.
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.
You are welcome :)
MODERATOR
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |