This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Feb 26, 2013 2:26 AM by Paul Horth RSS

Trigger on a table doesnt update last inserted

991289 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    try to use "after" trigger or "instead of"
  • 2. Re: Trigger on a table doesnt update last inserted
    991289 Newbie
    Currently Being Moderated
    What do you mean?
  • 3. Re: Trigger on a table doesnt update last inserted
    Mihael Pro
    Currently Being Moderated
    "before" trigger does not see last record
  • 4. Re: Trigger on a table doesnt update last inserted
    789895 Expert
    Currently Being Moderated
    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_Arp Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points