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)
icon5.gif  Tables not updating... even after commit. [message #357046] Mon, 03 November 2008 12:21 Go to next message
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 Go to previous messageGo to next message
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 #357053 is a reply to message #357046] Mon, 03 November 2008 13:10 Go to previous messageGo to next message
bcteh
Messages: 2
Registered: October 2008
Junior Member

May be you can check your base table
is updated or not.
dbms_output.PUT_LINE(SQL%ROWCOUNT);

Regards
Teh


Re: Tables not updating... even after commit. [message #357065 is a reply to message #357050] Mon, 03 November 2008 14:03 Go to previous message
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... Confused

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?
Previous Topic: Print all Columns and tables
Next Topic: how to write this query
Goto Forum:
  


Current Time: Mon Nov 04 10:52:40 CST 2024