1 2 Previous Next 21 Replies Latest reply: Feb 26, 2013 4:26 AM by Paul Horth RSS

    Trigger on a table doesnt update last inserted

    991289
      Hello all,

      i have a problem i got a trigger on a table before insert. It works fine except it doesn't update last record inserted into table.

      Code is below:
      create or replace
      TRIGGER id_on
      before INSERT ON table_ids
      REFERENCING NEW AS NEW FOR EACH ROW 
      declare
       pragma autonomous_transaction;
      begin
      
      
      MERGE INTO table_ids A
           USING (SELECT ID_NR
                       , ROW_NUMBER () OVER (order by date_to,ID_NR) AS val
                    FROM table_ids) b
              ON (a.ID_NR = B.ID_NR)
      WHEN MATCHED
      THEN
      UPDATE SET ID_REAL = val;
         
      COMMIT;
      
      UPDATE table_ids
      SET left_pay = pay
      WHERE ID_Type= 'P'
      AND date_to IS NULL;
      COMMIT;
      
      end;
        • 1. Re: Trigger on a table doesnt update last inserted
          Mihael
          try to use "after" trigger or "instead of"
          • 2. Re: Trigger on a table doesnt update last inserted
            991289
            What do you mean?
            • 3. Re: Trigger on a table doesnt update last inserted
              Mihael
              "before" trigger does not see last record
              • 4. Re: Trigger on a table doesnt update last inserted
                789895
                Hi,

                The before insert trigger is executed before the actual record is inserted in the table. Hence you are facing the issue of the last inserted record not having the changes you intended to make. Change your trigger to after insert then you will have the required result.

                cheers

                VT
                • 5. Re: Trigger on a table doesnt update last inserted
                  _Karthick_
                  Matt Valkonn wrote:
                  Hello all,

                  i have a problem i got a trigger on a table before insert. It works fine except it doesn't update last record inserted into table.

                  Code is below:
                  create or replace
                  TRIGGER id_on
                  before INSERT ON table_ids
                  REFERENCING NEW AS NEW FOR EACH ROW 
                  declare
                  pragma autonomous_transaction;
                  begin
                  
                  
                  MERGE INTO table_ids A
                  USING (SELECT ID_NR
                  , ROW_NUMBER () OVER (order by date_to,ID_NR) AS val
                  FROM table_ids) b
                  ON (a.ID_NR = B.ID_NR)
                  WHEN MATCHED
                  THEN
                  UPDATE SET ID_REAL = val;
                  
                  COMMIT;
                  
                  UPDATE table_ids
                  SET left_pay = pay
                  WHERE ID_Type= 'P'
                  AND date_to IS NULL;
                  COMMIT;
                  
                  end;
                  Can you tell what you are trying to achieve? You code is WRONG, simpley WRONG.

                  NEVER.. NEVER.. use AUTONOMOUS_TRANSACTION in TRIGGER. Its just a BAD.. VERY VERY BAD idea.

                  Trigger is just an event caused by a DML operation. It is designed to be part of a transaction, not a independent transaction. By using AUTONOMOUS_TRANSACTION you are isolating the trigger as a independent transaction.

                  I am telling you just drop that trigger and let us know what is your Business requirement.
                  • 6. Re: Trigger on a table doesnt update last inserted
                    Paul  Horth
                    Matt Valkonn wrote:
                    Hello all,

                    i have a problem i got a trigger on a table before insert. It works fine except it doesn't update last record inserted into table.

                    Code is below:
                    create or replace
                    TRIGGER id_on
                    before INSERT ON table_ids
                    REFERENCING NEW AS NEW FOR EACH ROW 
                    declare
                    pragma autonomous_transaction;
                    begin
                    
                    
                    MERGE INTO table_ids A
                    USING (SELECT ID_NR
                    , ROW_NUMBER () OVER (order by date_to,ID_NR) AS val
                    FROM table_ids) b
                    ON (a.ID_NR = B.ID_NR)
                    WHEN MATCHED
                    THEN
                    UPDATE SET ID_REAL = val;
                    
                    COMMIT;
                    
                    UPDATE table_ids
                    SET left_pay = pay
                    WHERE ID_Type= 'P'
                    AND date_to IS NULL;
                    COMMIT;
                    
                    end;
                    No, no no! NEVER commit inside a trigger.

                    What are you actually trying to do?
                    • 7. Re: Trigger on a table doesnt update last inserted
                      991289
                      Hello,

                      Why i use pragma autonomous_transaction? Because i get mutating trigger error otherwise.

                      What i need to do is update each record with an id_nr which is a number (without gaps) after each record is inserted.

                      (the second part of trigger is just a simple update which is there only in testing phase).

                      If you have better idea let me know. (i know its wrong).

                      Thank you.

                      ps

                      i have changed it to (below code) and still same resoult ... last record doesnt get updated.
                      create or replace
                      TRIGGER id_on
                      after INSERT ON table_ids
                      REFERENCING NEW AS NEW FOR EACH ROW 
                      declare
                       pragma autonomous_transaction;
                      begin
                       
                       
                      MERGE INTO table_ids A
                           USING (SELECT ID_NR
                                       , ROW_NUMBER () OVER (order by date_to,ID_NR) AS val
                                    FROM table_ids) b
                              ON (a.ID_NR = B.ID_NR)
                      WHEN MATCHED
                      THEN
                      UPDATE SET ID_REAL = val;
                         
                      COMMIT;
                       
                      UPDATE table_ids
                      SET left_pay = pay
                      WHERE ID_Type= 'P'
                      AND date_to IS NULL;
                      COMMIT;
                       
                      end;
                      Edited by: Matt Valkonn on 26.2.2013 0:18
                      • 8. Re: Trigger on a table doesnt update last inserted
                        Mihael
                        Why i use pragma autonomous_transaction? Because i get mutating trigger error otherwise.
                        create a view and use "instead of insert" trigger on it
                        • 9. Re: Trigger on a table doesnt update last inserted
                          BluShadow
                          I agree with Karthick and Paul, your trigger is seriously flawed.

                          a) autonomous transactions in a trigger indicate a lack of understanding of how transactions work
                          b) committing inside a trigger indicates a lack of understanding of how transactions work

                          I can't quite tell what your trigger is trying to do, but it looks something like you are trying to create some sort of sequence on your records.... let me guess... you're trying to create a gapless sequence for a group of records?

                          As already mentioned, it would be better if you explained what you are trying to achieve and then we can advise you better on how to go about it.
                          • 10. Re: Trigger on a table doesnt update last inserted
                            BluShadow
                            Mihael wrote:
                            Why i use pragma autonomous_transaction? Because i get mutating trigger error otherwise.
                            create a view and use "instead of insert" trigger on it
                            No. What benefit would that give?
                            • 11. Re: Trigger on a table doesnt update last inserted
                              991289
                              BluShadow wrote:
                              I can't quite tell what your trigger is trying to do, but it looks something like you are trying to create some sort of sequence on your records.... let me guess... you're trying to create a gapless sequence for a group of records?
                              Exactly. I need to have gaples sequence of inserted rows.
                              • 12. Re: Trigger on a table doesnt update last inserted
                                Mihael
                                create a view and use "instead of insert" trigger on it
                                No. What benefit would that give?
                                He will be able to select from base table.
                                • 13. Re: Trigger on a table doesnt update last inserted
                                  BluShadow
                                  Ah, you replied while I was typing... :)
                                  Matt Valkonn wrote:
                                  Why i use pragma autonomous_transaction? Because i get mutating trigger error otherwise.
                                  Then that's a good indication that you shouldn't be using a trigger, and you're doing your process in the wrong place.
                                  What i need to do is update each record with an id_nr which is a number (without gaps) after each record is inserted.
                                  When you try and create an id that is 'gapless' then you are going to have to account for the fact that this prevents the application from working in a multi-user environment (albeit temporarily). In which case you will need to, in essence, lock the table (or group of records) to prevent other users from accessing them, update your id's as required, then commit and release the records for others.

                                  But firstly, a question... why do the id's need to be gapless? What purpose would a gapless id serve?
                                  • 14. Re: Trigger on a table doesnt update last inserted
                                    BluShadow
                                    Mihael wrote:
                                    create a view and use "instead of insert" trigger on it
                                    No. What benefit would that give?
                                    He will be able to select from base table.
                                    Would you care to demonstrate how that's going to help him create a gapless sequence for his records?
                                    1 2 Previous Next