Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2004
    Posts
    7

    Question Unanswered: Oracle update trigger

    Hi,

    Im trying to do an update trigger over a view of two tables.

    My view is very simple, i already did an insert trigger thats working great, but in the case of the update trigger i stumbled with this that kept me scratching my head for a while. (The solution may be very simple but im no oracle expert)

    My question is ...
    In the insert trigger i used ":new" as a pseudo record to know which fields had data, in update's case, how can i know the update fields in the where clause ?

    I mean ..
    In order to know what clause i must use in the update queries over the view components ...


    TIA
    Martin

  2. #2
    Join Date
    Dec 2004
    Posts
    7

    To be more precise ...

    I just wanna know whats after the WHERE ...

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Are you writing an INSTEAD OF trigger on a view?

    If so, your code would be something like:

    update table1
    set col1 = :new.col1, col2 = :new.col2, ...
    where keycol = :old.keycol;

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    perhaps you need to look into Materialized Views with a Refresh on Commit.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Dec 2004
    Posts
    7

    Thanks for the quick replies ...

    Quote Originally Posted by andrewst
    Are you writing an INSTEAD OF trigger on a view?

    If so, your code would be something like:

    update table1
    set col1 = :new.col1, col2 = :new.col2, ...
    where keycol = ld.keycol;
    Yes i am.

    I see your point, but how do i know which fields are the keycols ?
    The problem i have is that my table in the view has more or less 280 fields, to be size wise they are distributed in one base table with the key columns and in a "secondary table" some 40 fileds that repeats 6 times in the view with different meanings.

    The idea is not making a big 280 fields table and make a fake table with the view and the triggers ...

    In this case, i need to know how should filter the tables that are related in a 1:1 basis and my question is ...

    Should i check one by one all the different fields against "ld" to know which ones are to be used in the Where Clause ?
    And again with the ":new" not know which values to change ?


    Regards

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by tacho_fortin
    I see your point, but how do i know which fields are the keycols ?
    You have to know the key columns to write the trigger. The trigger can't "work out" the key columns by inspecting the data. If you don't know, then you need to find out - e.g. ask the person who designed the database. I can't help you with that.

  7. #7
    Join Date
    Dec 2004
    Posts
    7

    Lightbulb

    Thats odd, how can i replicate data in secondary tables without knowing the filtering condition ?

    Anyway, im using "Instead Of" and "FOR EACH ROW" ergo i pressume this is doing a row by row update.

    In that case i should check each field in :NEW against any other in :OLD to see whats changing ?

    So few information in the manuals ...

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The trigger knows what is changing. The idea of an INSTEAD OF trigger is to apply the changes to the base tables of the view. Perhaps a simple (rather unrealistic) example will help:
    Code:
    create view v_emp as
    select emp.empno, emp.ename, emp.deptno, dept.dname
    from emp, dept
    where emp.deptno = dept.deptno;
    
    create trigger v_emp_trg
    instead of update on v_emp
    for each row
    begin
      update emp
      set ename = :new.ename, deptno = :new.deptno
      where empno = :old.empno;
      update dept
      set dname = :new.dname
      where deptno = :old.deptno;
    end;
    /
    Now whenever you try to update the view, the trigger actually updates the underlying emp and dept tables. It knows which rows to update because the view includes the keys of the tables to be updated (it would not be possible otherwise).

    If you don't find this helpful, you will probably need to post your table and view definitions, along with the logic of what you want the trigger to do.

  9. #9
    Join Date
    Dec 2004
    Posts
    7

    Post Thanks for the reply

    I couldnt even make your little test run in this server.

    For clarity sake here are the scripts.

    The idea is that it represents harmonic values measured over 3 phases with values for current and energy. (its from an electric utility company)


    Anyway, i detected that update triggers dont fire but compiles ok.
    I cannot even debug them, something i can do with the insert trigger.

    TIA for any help and your time.


    Regards
    Martin
    Attached Files Attached Files

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by tacho_fortin
    I couldnt even make your little test run in this server.
    Why not?

    Your script is rather large and hard to take in (horrible table design by the way!), but one thing I notice is code like this:

    IF :NEW.F1VARMONICO2 != :OLD.F1VARMONICO2 THEN

    That will not work if one of the values is null. You should probably be doing this instead:

    IF :NEW.F1VARMONICO2 != :OLD.F1VARMONICO2
    OR (:NEW.F1VARMONICO2 IS NULL AND :OLD.F1VARMONICO2 IS NOT NULL)
    OR (:NEW.F1VARMONICO2 IS NOT NULL AND :OLD.F1VARMONICO2 IS NULL)
    THEN

  11. #11
    Join Date
    Dec 2004
    Posts
    7
    I know the design is horrible, but the vb code that comes with that design is worse.
    My last resort is making a table like the view but want to make a better approach without messing the vb code and/or making a huge table with a lot of space wasted.

    BTW, its meaningful to check only the first value, but thats beside the point.

    Thanks anyway.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm starting to lose the thread here...

    1) Did you understand that this comparision will fail if either value is null? :-
    Code:
    IF :NEW.F1VARMONICO2 != :OLD.F1VARMONICO2 THEN...
    i.e. if F1VARMONICO2 was null and you update it to 99 then the trigger won't do anything.

    2) You said earlier that the update trigger isn't firing - how do you know?

    As an aside, you can dispense with the SELECT statements in the trigger by doing it this way:
    Code:
    		UPDATE templecturaarmonico SET 
    			ARMONICO2 = :NEW.F1VARMONICO2, ARMONICO3 = :NEW.F1VARMONICO3, ARMONICO4 = :NEW.F1VARMONICO4, ARMONICO5 = :NEW.F1VARMONICO5, ARMONICO6 = :NEW.F1VARMONICO6, ARMONICO7 = :NEW.F1VARMONICO7, ARMONICO8 = :NEW.F1VARMONICO8, ARMONICO9 = :NEW.F1VARMONICO9, ARMONICO10 = :NEW.F1VARMONICO10, ARMONICO11 = :NEW.F1VARMONICO11,
    			ARMONICO12 = :NEW.F1VARMONICO12, ARMONICO13 = :NEW.F1VARMONICO13, ARMONICO14 = :NEW.F1VARMONICO14, ARMONICO15 = :NEW.F1VARMONICO15, ARMONICO16 = :NEW.F1VARMONICO16, ARMONICO17 = :NEW.F1VARMONICO17, ARMONICO18 = :NEW.F1VARMONICO18, ARMONICO19 = :NEW.F1VARMONICO19, ARMONICO20 = :NEW.F1VARMONICO20, ARMONICO21 = :NEW.F1VARMONICO21, 
    			ARMONICO22 = :NEW.F1VARMONICO22, ARMONICO23 = :NEW.F1VARMONICO23, ARMONICO24 = :NEW.F1VARMONICO24, ARMONICO25 = :NEW.F1VARMONICO25, ARMONICO26 = :NEW.F1VARMONICO26, ARMONICO27 = :NEW.F1VARMONICO27, ARMONICO28 = :NEW.F1VARMONICO28, ARMONICO29 = :NEW.F1VARMONICO29, ARMONICO30 = :NEW.F1VARMONICO30, ARMONICO31 = :NEW.F1VARMONICO31,
    			ARMONICO32 = :NEW.F1VARMONICO32, ARMONICO33 = :NEW.F1VARMONICO33, ARMONICO34 = :NEW.F1VARMONICO34, ARMONICO35 = :NEW.F1VARMONICO35, ARMONICO36 = :NEW.F1VARMONICO36, ARMONICO37 = :NEW.F1VARMONICO37, ARMONICO38 = :NEW.F1VARMONICO38, ARMONICO39 = :NEW.F1VARMONICO39, ARMONICO40 = :NEW.F1VARMONICO40 
    		WHERE IDUSUARIO = :OLD.IDUSUARIO AND IDPERIODO = :OLD.IDPERIODO AND IDOCEBA = :OLD.IDOCEBA AND IDTIPOMEDICION = :OLD.IDTIPOMEDICION AND IDITEM = :OLD.IDITEM AND IDORDINAL = :OLD.IDORDINAL AND FECHA = :OLD.FECHA AND HORA = :OLD.HORA AND idtipofase = '1' AND tipo = 'V';
    		IF sql%rowcount = 0 THEN
    			INSERT INTO templecturaarmonico
    				(IDUSUARIO,IDPERIODO,IDOCEBA,IDTIPOMEDICION,IDITEM,IDORDINAL,FECHA,HORA,idtipofase,tipo,ARMONICO2,ARMONICO3,ARMONICO4,ARMONICO5,ARMONICO6,ARMONICO7,ARMONICO8,ARMONICO9,ARMONICO10,ARMONICO11,ARMONICO12,ARMONICO13,ARMONICO14,ARMONICO15,ARMONICO16,ARMONICO17,ARMONICO18,ARMONICO19,ARMONICO20,ARMONICO21,ARMONICO22,ARMONICO23,ARMONICO24,ARMONICO25,ARMONICO26,ARMONICO27,ARMONICO28,ARMONICO29,ARMONICO30,ARMONICO31,ARMONICO32,ARMONICO33,ARMONICO34,ARMONICO35,ARMONICO36,ARMONICO37,ARMONICO38,ARMONICO39,ARMONICO40)		  
     			VALUES
    				  (:NEW.IDUSUARIO,:NEW.IDPERIODO,:NEW.IDOCEBA,:NEW.IDTIPOMEDICION,:NEW.IDITEM,:NEW.IDORDINAL,:NEW.FECHA,:NEW.HORA,'1','V',:NEW.F1VARMONICO2,:NEW.F1VARMONICO3,:NEW.F1VARMONICO4,:NEW.F1VARMONICO5,:NEW.F1VARMONICO6,:NEW.F1VARMONICO7,:NEW.F1VARMONICO8,:NEW.F1VARMONICO9,:NEW.F1VARMONICO10,:NEW.F1VARMONICO11,:NEW.F1VARMONICO12,:NEW.F1VARMONICO13,:NEW.F1VARMONICO14,:NEW.F1VARMONICO15,:NEW.F1VARMONICO16,:NEW.F1VARMONICO17,:NEW.F1VARMONICO18,:NEW.F1VARMONICO19,:NEW.F1VARMONICO20,:NEW.F1VARMONICO21,:NEW.F1VARMONICO22,:NEW.F1VARMONICO23,:NEW.F1VARMONICO24,:NEW.F1VARMONICO25,:NEW.F1VARMONICO26,:NEW.F1VARMONICO27,:NEW.F1VARMONICO28,:NEW.F1VARMONICO29,:NEW.F1VARMONICO30,:NEW.F1VARMONICO31,:NEW.F1VARMONICO32,:NEW.F1VARMONICO33,:NEW.F1VARMONICO34,:NEW.F1VARMONICO35,:NEW.F1VARMONICO36,:NEW.F1VARMONICO37,:NEW.F1VARMONICO38,:NEW.F1VARMONICO39,:NEW.F1VARMONICO40);
    		END IF;

  13. #13
    Join Date
    Dec 2004
    Posts
    7
    Oops ...
    Sorry, i didnt think through what you were talking about.

    No, i didnt.

    I rechecked the view and saw that wasnt working either in some cases (the ones i was checking the trigger)

    Assumed that null - null thing in those cases too and thats the reason why the trigger didnt fire.
    No rows selected i guess

    Thanks for your help !
    At least now i can make a proper debugging.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •