Home » SQL & PL/SQL » SQL & PL/SQL » Tables not updating... even after commit. (PL/SQL 9.2.0.4 on a 9.2.0.4 EE DB)
Tables not updating... even after commit. [message #357046] |
Mon, 03 November 2008 12:21 |
rm_mainframe
Messages: 8 Registered: October 2008 Location: Liverpool, UK
|
Junior Member |
|
|
Hello,
I'm trying to update a view consisting of three tables using an 'instead of' trigger.
Everything appears to be okay at face value: the trigger is firing correctly (see dbms_output.put_line's), Old and new values are being parsed into the trigger correctly (see put_line(s), but yet the base table(s) of the view don't update... Can anyone shed any light on this?
I've been quite thorough in testing this before posting: I've double checked that the necessary permissions are correct, and as you'll see, the lack of any error message doesn't help.
Any ideas?
Ron.
---------------------------------------------------------
This is the view :
---------------------------------------------------------
CREATE OR REPLACE VIEW V_WMS_USER_SUMMARY as
select a.fxm_username, a.fxm_description, b.warehouse_id,
b.company_code, a.fxm_security_level,
c.stockist_code, c.stockist_sub_code,
a.fxm_class, a.fxm_menu_method, a.fxm_language,
a.delete_flag
from fxm_users a,
flex_user_comp_warehouses b,
flex_user_comp_owners c
where a.fxm_username = b.logon_id
and b.logon_id = c.logon_id
and b.company_code = c.company_code;
---------------------------------------------------------
This is the Trigger:
---------------------------------------------------------
create or replace trigger trg_wms_user_summary_update
instead of update on v_wms_user_summary
begin
dbms_output.put_line('Trigger fired');
update fxm_users
set fxm_username = :new.fxm_username,
fxm_description = :new.fxm_description,
fxm_security_level = :new.fxm_security_level,
fxm_class = :new.fxm_class,
fxm_menu_method = :new.fxm_menu_method,
fxm_language = :new.fxm_language,
delete_flag = :new.delete_flag
where
fxm_username = :old.fxm_username and
fxm_description = :old.fxm_description and
fxm_security_level = :old.fxm_security_level and
fxm_class = :old.fxm_class and
fxm_menu_method = :old.fxm_menu_method and
fxm_language = :old.fxm_language and
delete_flag = :old.delete_flag;
dbms_output.put_line(:new.fxm_security_level || :old.fxm_security_level);
update flex_user_comp_warehouses
set logon_id = :new.fxm_username,
warehouse_id = :new.warehouse_id,
company_code = :new.company_code
where
logon_id = :old.fxm_username and
warehouse_id = :old.warehouse_id and
company_code = :old.company_code;
dbms_output.put_line(:new.fxm_security_level || :old.fxm_security_level);
update flex_user_comp_owners
set company_code = :new.company_code,
logon_id = :new.fxm_username,
stockist_code = :new.stockist_code,
stockist_sub_code = :new.stockist_sub_code
where
company_code = :old.company_code and
logon_id = :old.fxm_username and
stockist_code = :old.stockist_code and
stockist_sub_code = :old.stockist_sub_code;
dbms_output.put_line(:new.fxm_security_level || :old.fxm_security_level);
end;
/
---------------------------------------------------------
Here is what happens:
---------------------------------------------------------
SQL> select fxm_security_level
from v_wms_user_summary
where fxm_username = 'RM10';
FXM_SECURITY_LEVEL
------------------
8
SQL> update v_wms_user_summary
set fxm_security_level ='5'
where fxm_username = 'RM10';
Trigger fired
58
58
58
1 row updated.
SQL> commit;
Commit complete.
SQL> select fxm_security_level from v_wms_user_summary
where fxm_username = 'RM10';
FXM_SECURITY_LEVEL
------------------
8
--------------------------------------------------------
As you can see even after a commit, the data hasn't changed... Does anyone have any ideas as to why not?
|
|
|
Re: Tables not updating... even after commit. [message #357050 is a reply to message #357046] |
Mon, 03 November 2008 12:52 |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@rm_mainframe,
I don't remember the exact web site where I picked these up sometime back but I do hope these points especially the third one might interest you.
Quote: |
If you want a join view to be updatable, then all of the following conditions must be true:
1. The DML statement must affect only one table underlying the join.
2. For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.
3. For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view was created WITH CHECK OPTION, then join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.
|
Can you post some sort of test case with create table statements and some dummy records so that we can work on?
Hope this helps.
Regards,
Jo
|
|
|
|
Re: Tables not updating... even after commit. [message #357065 is a reply to message #357050] |
Mon, 03 November 2008 14:03 |
rm_mainframe
Messages: 8 Registered: October 2008 Location: Liverpool, UK
|
Junior Member |
|
|
I revisited the trigger after looking at those three points posted earlier. One thing that occurred to me, is that the first update statement has too many where clauses in it.
The PK for table 1 of the view is 'column': fxm_username, so I don't need any other where statements to find an individual row in table fxm_users...
So, in the trigger, I've commented out all the other superflous where clauses:
create or replace trigger trg_wms_user_summary_update
instead of update on v_wms_user_summary
begin
dbms_output.put_line('Trigger fired');
update fxm_users
set fxm_username = :new.fxm_username,
fxm_description = :new.fxm_description,
fxm_security_level = :new.fxm_security_level,
fxm_class = :new.fxm_class,
fxm_menu_method = :new.fxm_menu_method,
fxm_language = :new.fxm_language,
delete_flag = :new.delete_flag
where
fxm_username = :old.fxm_username; -- and
-- fxm_description = :old.fxm_description and
-- fxm_security_level = :old.fxm_security_level and
-- fxm_class = :old.fxm_class and
-- fxm_menu_method = :old.fxm_menu_method and
-- fxm_language = :old.fxm_language and
-- delete_flag = :old.delete_flag;
... REST OF TRIGGER... ...REST OF TRIGGER... ...REST OF TRIGGER...
For my first test, I'll update fxm_security_level again.
Apparently, the problem is solved...
SQL> update v_wms_user_summary set FXM_SECURITY_LEVEL = '5'
where fxm_username = 'RM10';
Trigger fired
58
58
58
1 row updated.
SQL> commit;
Commit complete.
SQL> select fxm_security_level from v_wms_user_summary where
fxm_username = 'RM10';
FXM_SECURITY_LEVEL
------------------
5
1 row selected.
Test 1: Pass.
Next test, is to update data from base tables, 2 and 3, and then try all the base tables together at once.
Table 2:
SQL> select warehouse_id from v_wms_user_summary where
fxm_username = 'RL10';
WAR
---
333
1 row selected.
SQL> update v_wms_user_summary set warehouse_id = '999' where fxm_username = 'RLWM10';
Trigger fired
1 row updated.
SQL> commit;
Commit complete.
SQL> select warehouse_id from v_wms_user_summary where
fxm_username = 'RM10';
WAR
---
999
1 row selected.
Test 2: That's a pass.
Table three:
SQL> select company_code from v_wms_user_summary where
fxm_username = 'RM10';
COM
---
000
1 row selected.
SQL> update v_wms_user_summary set company_code = '001' where
fxm_username = 'RM10';
Trigger fired
1 row updated.
SQL> commit;
Commit complete.
SQL> select company_code from v_wms_user_summary where
fxm_username = 'RM10';
COM
---
001
1 row selected.
Test 3:That's another pass...
Now to try all three tables together in the one update, as would happen in the real world, when a user makes a real-word change to multiple values:
SQL> desc v_wms_user_summary
Name Null? Type
------------------------------- -------- ----
FXM_USERNAME NOT NULL VARCHAR2(12)
FXM_DESCRIPTION VARCHAR2(30)
WAREHOUSE_ID NOT NULL VARCHAR2(3)
COMPANY_CODE NOT NULL VARCHAR2(3)
FXM_SECURITY_LEVEL NOT NULL NUMBER(1)
STOCKIST_CODE NOT NULL VARCHAR2(10)
STOCKIST_SUB_CODE NOT NULL VARCHAR2(3)
FXM_CLASS VARCHAR2(12)
FXM_MENU_METHOD VARCHAR2(1)
FXM_LANGUAGE VARCHAR2(3)
DELETE_FLAG NOT NULL VARCHAR2(1)
SQL> select FXM_SECURITY_LEVEL, WAREHOUSE_ID, STOCKIST_CODE from v_wms_user_summary
2 where FXM_USERNAME = 'RLWM10';
FXM_SECURITY_LEVEL WAR STOCKIST_C
------------------ --- ----------
5 999 E55
1 row selected.
SQL> update v_wms_user_summary
2 set
3 FXM_SECURITY_LEVEL = '9',
4 WAREHOUSE_ID = '333',
5 STOCKIST_CODE = '111'
6 where FXM_USERNAME = 'RM10';
Trigger fired
1 row updated.
SQL> commit;
Commit complete.
SQL> select FXM_SECURITY_LEVEL, WAREHOUSE_ID, STOCKIST_CODE from
v_wms_user_summary
2 where FXM_USERNAME = 'RM10';
FXM_SECURITY_LEVEL WAR STOCKIST_C
------------------ --- ----------
9 333 111
1 row selected.
Test 4: That's a pass....
The problem is resolved, the update of the view is now updating the base tables correctly, but, why did removing all the unnessary where clauses from the first part of the trigger (table 1), fix it?
|
|
|
Goto Forum:
Current Time: Mon Nov 04 10:52:40 CST 2024
|