Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2011
    Posts
    20

    Question Unanswered: Mutating table Oracle

    Hy! I'm new to oracle and i'm trying to create a trigger that gets a value from a table (date) and with this value calculates a new value (difference between sysdate and this date) and tryes to update again in the main table, I know this is wrog, but how to make it right?

    CREATE OR REPLACE TRIGGER AMC_VAR1.update_fields
    AFTER INSERT OR UPDATE
    ON AMC_VAR1.GA_PIPE
    REFERENCING NEW AS New OLD AS Old
    FOR EACH ROW
    DECLARE
    years numeric(10);
    BEGIN

    if(updating) then
    if (:New.DATA_PIF <> :Old.DATA_PIF) then
    select TO_CHAR(SYSDATE, 'YYYY')-TO_CHAR(DATA_PIF,'YYYY') into years from GA_PIPE WHERE FID=:New.FID;
    end if;
    end if;
    UPDATE GA_PIPE SET VECHIME=years WHERE FID=:New.FID;

    END ;

    I already tryed autonomous transaction but i get an error with dead lock. Pleasa help me, this is urgent.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(DATA_PIF,'YYYY')
    This doesn't make sense at all. You are subtracting a character value from another. You probably want: extract(year from sysdate) - extract(year from data_pif) instead.

    Now to your underlying problem: there is no need to execute an UPDATE statement if you want to change a column for the row that is affected by the trigger. Simply assign the new value:
    Code:
    CREATE OR REPLACE TRIGGER AMC_VAR1.update_fields
      BEFORE INSERT OR UPDATE ON AMC_VAR1.GA_PIPE
      FOR EACH ROW
    BEGIN
      if (INSERTING or :New.DATA_PIF <> :Old.DATA_PIF) then
        :NEW.VECHIME := extract(year FROM SYSDATE) - extract(year FROM :NEW.DATA_PIF);
      end if;
    END;
    But this can only be done in a BEFORE trigger, therefor I changed (and simplified) the trigger definition as well.

    As the result of an expression can directly be assigned to a column, there is no need for the variable years

    I assumed you want to do the calculation also when inserting new values, so I changed (and simplified) your IF condition as well.

    And please, for future posts, use [code] tags to make the SQL code readable.
    More details on the available tags can be found here: http://www.dbforums.com/misc.php?do=bbcode

  3. #3
    Join Date
    Apr 2011
    Posts
    20
    Thank You very much for your answer but the problem is that the field VECHIME remains empty. The field VECHIME will be calculated after importing DATA_PIF that is why I think a need after insert or update.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >the problem is that the field VECHIME remains empty.
    was COMMIT ever issued?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2011
    Posts
    20
    I inserted values in the table.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I inserted values in the table.
    Which is separate & distinct from what I asked.

    was COMMIT ever issued?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by amalia.stan View Post
    Thank You very much for your answer but the problem is that the field VECHIME remains empty.
    What exactly do you mean with that? Even with my version of the trigger (I tried it and it worked for me)

    If DATA_PIF is NULL for the updated or inserted row, then of course the VECHIME will be null as well. You will need to tell us, what should happen if DATA_PIF is null in the trigger

    The field VECHIME will be calculated after importing DATA_PIF that is why I think a need after insert or update.
    What kind of import are you running?

  8. #8
    Join Date
    Apr 2011
    Posts
    20
    I inserted DATA_PIF=01.07.2007 and VECHIME should be calculated as
    Code:
    extract(year FROM SYSDATE) - extract(year FROM :NEW.DATA_PIF
    instead of this , VECHIME is empty

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    storing calculated/computed values in static table is a seriously FLAWED design.
    any result calculated by trigger today could be incorrect in the future.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by amalia.stan View Post
    I inserted DATA_PIF=01.07.2007 and VECHIME should be calculated as
    Code:
    extract(year FROM SYSDATE) - extract(year FROM :NEW.DATA_PIF
    instead of this , VECHIME is empty
    What data type is the DATA_PIF column?
    Can you show use the full CREATE TABLE statement for that table?

  11. #11
    Join Date
    Apr 2011
    Posts
    20
    I tryed inserting data in my table and you are right after commit VECHIME value changes. My problem is that i'm using Topobase client (i don't know if you are familiar with Autodesk products - Topobase) and when i'm inserting a new pipe and complete the information on the form (including DATA_PIF) then VECHIME is empty.
    this is the create statament of the ga_pipe table

    [code]
    ALTER TABLE AMC_VAR1.GA_PIPE
    DROP PRIMARY KEY CASCADE;
    DROP TABLE AMC_VAR1.GA_PIPE CASCADE CONSTRAINTS;

    CREATE TABLE AMC_VAR1.GA_PIPE
    (
    FID NUMBER(10),
    CADASTRAL_INFO VARCHAR2(255 BYTE),
    DATE_ACQUIRED DATE,
    DATE_CREATION DATE,
    DATE_INSTALLATION DATE,
    DATE_STARTUP DATE,
    FID_CONTACT_CONCESSIONAIRE NUMBER(10),
    FID_CONTACT_INSTALLER NUMBER(10),
    FID_CONTACT_MAINTENANCE NUMBER(10),
    FID_CONTACT_OPERATOR NUMBER(10),
    FID_CONTACT_OWNER NUMBER(10),
    FID_LOCATION NUMBER(10),
    FID_PRESSUREZONE NUMBER(10),
    FID_SUPPLYZONE NUMBER(10),
    GROUND_ELEVATION NUMBER(20,8),
    ID_ACCURACY NUMBER(10),
    ID_CATHODIC_PROTECTION NUMBER(10),
    ID_CONDITION NUMBER(10),
    ID_CONNECTION_TYPE NUMBER(10),
    ID_DISPOSITION_STATE NUMBER(10),
    ID_FUEL_TYPE NUMBER(10),
    ID_FUNCTION NUMBER(10),
    ID_LAYING_TYPE NUMBER(10),
    ID_LOCATION_TYPE NUMBER(10),
    ID_MARKER_TYPE NUMBER(10),
    ID_OWNERSHIP_TYPE NUMBER(10),
    ID_RENOVATION NUMBER(10),
    ISOLATION VARCHAR2(255 BYTE),
    LOCATION VARCHAR2(255 BYTE),
    NAME_NUMBER VARCHAR2(255 BYTE),
    NARRATIVE VARCHAR2(2000 BYTE),
    PIPE_LENGTH NUMBER(20,8),
    PRESSURE_MAX NUMBER(20,8),
    PRESSURE_OPERATING NUMBER(20,8),
    SERIAL_NUMBER VARCHAR2(255 BYTE),
    SLOPE_MEASURED NUMBER(20,8),
    USER_FLAG VARCHAR2(255 BYTE),
    CERTIFICATE VARCHAR2(10 BYTE),
    DIAMETER_INSIDE NUMBER(20,8),
    DIAMETER_NOMINAL VARCHAR2(10 BYTE),
    DIAMETER_OUTSIDE NUMBER(20,8),
    FID_MANUFACTURER NUMBER(10),
    ID_COATING_INSIDE NUMBER(10),
    ID_COATING_OUTSIDE NUMBER(10),
    ID_MATERIAL NUMBER(10),
    ID_PIPE_TYPE NUMBER(10),
    THICKNESS NUMBER(20,8),
    VALUE VARCHAR2(255 BYTE),
    DATA_PIF DATE,
    DIAMETRU_NOMINAL VARCHAR2(10 BYTE),
    ZONA_AMPLASARE NUMBER(10),
    POSIBILITATI_INFILTRARE NUMBER(10),
    VECHIME NUMBER(10) DEFAULT 0
    )

  12. #12
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    OK, so DATA_PIF is a DATE which is good.

    If VECHIME is NULL after insert, my bet is that DATA_PIF was not supplied.

    So what should be the result of the calculation (for the YEARS column) if DATA_PIF is null?

  13. #13
    Join Date
    Apr 2011
    Posts
    20
    DATA_PIF is supplied becouse in the table exists. this is a print screen. Where VECHIME is empty was after i inserted value in the form with the trigger you gave me. Where VECHME has a value , not 0, was after i inserted values directly in the table and a did a commit.


    http://www.blacklight.ro/externalcontent/ga_pipe.JPG

  14. #14
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I'm not sure what you mean with "i inserted value in the form with the trigger". A trigger does not insert anything. It is fired when you run an INSERT or UPDATE stament.

    Is it possible that those values existed in the table before you created the trigger and when updating the table, DATA_PIF did not change (VECHME will only be changed when DATA_PIF is changed)?

    I cannot explain this otherwise.

    Where VECHME has a value , not 0, was after i inserted values directly in the table and a did a commit.
    I am not sure I understand that sentence. Do you mean, when you manually run an INSERT everything is fine? Then apparently the trigger is working.

    How did the UPDATE statement look like that didn't cause VECHME to change?

    What happens when you run an UPDATE statement manually to change DATA_PIF? Does that update VECHME?

    You can always run an UPDATE to sync the VECHME values manually to fix the values for those rows that were there before the trigger was created.

    You can also remove the IF statement completely and always update VECHME in the trigger. That is a very slight overhead, but I don't think you would notice.

    And please close the code tags using [/code] (do read the link I provided and which is also available at the bottom of the page: "BB code")

  15. #15
    Join Date
    Apr 2011
    Posts
    20
    I mean that i inserted values through the interface - a form and at that time the trigger was created but still VECHIME is empty. I know a trigger does not insert anything.
    When i insert values manually is ok, VECHIME changes and if i update DATA_PIF VECHIME updates too.

Posting Permissions

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