Forum Stats

  • 3,838,561 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

Trigger in mutating error while update

User_IAP38
User_IAP38 Member Posts: 16 Green Ribbon

Hi

 This trigger works fine while insert the data but update the records (start_date) that throws 

  SQL Error [4091] [42000]: ORA-04091: table grks_trade is mutating, trigger/function may not see itORA-06512: at "TRG_bktrade_BI", line 11

ORA-04088: error during execution of trigger 'TRG_bktrade_BI'


can you help me how to update below condtion throw raise_application_error

if((:NEW.START_DATE >= rec.START_DATE and :NEW.START_DATE <=rec.END_DATE) then

 RAISE_APPLICATION_ERROR(-20001,'New record not a valid date');  


code :

TRIGGER TRG_bktrade_BI

before INSERT OR UPDATE 

ON grks_trade

FOR EACH ROW

begin

 IF :NEW.COUNTRY<>'MAA'  THEN

        RAISE_APPLICATION_ERROR(-20001,'New record should be country is MAA');

  END IF;

  for rec in (SELECT COUNTRY,GROUP_NUM,START_DATE,END_DATE from grks_trade WHERE GROUP_NUM = :NEW.GROUP_NUM)

  loop

  if((:NEW.START_DATE >= rec.START_DATE and :NEW.START_DATE <=rec.END_DATE) then

 RAISE_APPLICATION_ERROR(-20001,'New record not a valid date');  

  end if;  

  end loop;

  end;

Tagged:

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Jul 8, 2022 1:32AM Answer ✓

    Your whole design is wrong. In this case, your data model should enforce business restriction rules, not try to do it as a hack with a trigger. As Frank and SY point out, updates could and will lead to problems.

    Having start and end dates and trying to prevent overlaps is a cynical design. For each row, simply capture a date and whether or not it is a start or end one. Then present the data by merging starts and ends where appropriate. Here's a rough design of what I mean (not fully checked).

    --drop table grks_trade_base;
    --drop view grks_trade;
    
    create table grks_trade_base (
      country    varchar2(50) not null
    , group_num  number       not null
    , entry_date date         not null
    , is_start   number(1)    not null
    , constraint pk_grks_trade_base primary key (country, group_num, entry_date, is_start)
    , constraint chk_maa check (country = 'MAA')
    , constraint chk_is_start check (is_start in (0, 1))  
    );
    
    
    begin
      -- Defined as block so you can remove / add
      delete from grks_trade_base;
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate - 1, 0);    -- 1. Test for end without start
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate    , 1);    -- 2. Consecutive starts (need to merge) 
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.1, 1);    -- 2.     "
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.2, 0);    -- 2. Consecutive ends (need to merge)
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.3, 0);    -- 2.     "
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.4, 1);    -- 3. New start
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.5, 0);    -- 3. New end
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.6, 1);    -- 3. Start without end, so end should be null
      commit;
    end;
    /
    
    select * from grks_trade_base;
    
    COUNTRY  GROUP_NUM ENTRY_DATE              IS_START
    ------- ---------- --------------------- ----------
    MAA              1 07/07/2022 02:29:59            0
    MAA              1 08/07/2022 02:29:59            1
    MAA              1 08/07/2022 04:53:59            1
    MAA              1 08/07/2022 07:17:59            0
    MAA              1 08/07/2022 09:41:59            0
    MAA              1 08/07/2022 12:05:59            1
    MAA              1 08/07/2022 14:29:59            0
    MAA              1 08/07/2022 16:53:59            1
    
    
    8 rows selected.
    
    create view grks_trade as  
      select * 
      from grks_trade_base
      match_recognize(
        partition by country, group_num
        order by entry_date
        measures first(A.entry_date) start_date, last(B.entry_date) end_date
        pattern(A+ B*)
        define
          A as is_start = 1
        , B as is_start = 0
      );
      
    select * from grks_trade;
    
    
    COUNTRY  GROUP_NUM START_DATE            END_DATE             
    ------- ---------- --------------------- ---------------------
    MAA              1 08/07/2022 02:29:59   08/07/2022 09:41:59  
    MAA              1 08/07/2022 12:05:59   08/07/2022 14:29:59  
    MAA              1 08/07/2022 16:53:59                        
    
    
    3 rows selected.
    
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    Hi, @User_IAP38

    A FOR EACH ROW trigger on table t can't read other rows from table t itself. You can use an INSTEAD OF trigger in this case.

    What is the business rule you want to enforce? What exactly do you want the trigger to do? Do you want it to raise an error only when start_date of the row being changed is between start_date and end_date of some other row in the same table? How about if end_date is between start_date and end_date of another row? Or what if you have rows like this

    start_date   end_date
    01-May-2022  31-May-2022
    01-Jun-2022  30-Jun-2022
    

    and then you UPDATE the second row, changing the start_date from 01-Jun-2022 to 01-Apr-2022?

  • Stax
    Stax Member Posts: 130 Bronze Badge

    1) save  :NEW.START_DATE, :NEW.GROUP_NUM in collection

    2) in after trigger check  condtion and clear collection

    ps

    sometimes it is more convenient to use compound trigger

    pss

    consider  INSERT/UPDATE  in parallel sessions

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond

    It appears OP wants to maintain no overlap withing a group. Trigger alone will not do it since trigger will not see uncommitted changes made by opther sessions. We must serialize the process (not good if data is modifiedquite often) by issuing SELECT * FROM grks_trade WHERE GROUP_NUM = xxx FOR UPDATE before inserting/updating/deleting.

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond

    Actually we must lock whole table, not just existing rows for the group. Locking just existing rows for the group will not force other sessions to wait if inserting new rows for the same group.

    SY.

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Jul 8, 2022 1:32AM Answer ✓

    Your whole design is wrong. In this case, your data model should enforce business restriction rules, not try to do it as a hack with a trigger. As Frank and SY point out, updates could and will lead to problems.

    Having start and end dates and trying to prevent overlaps is a cynical design. For each row, simply capture a date and whether or not it is a start or end one. Then present the data by merging starts and ends where appropriate. Here's a rough design of what I mean (not fully checked).

    --drop table grks_trade_base;
    --drop view grks_trade;
    
    create table grks_trade_base (
      country    varchar2(50) not null
    , group_num  number       not null
    , entry_date date         not null
    , is_start   number(1)    not null
    , constraint pk_grks_trade_base primary key (country, group_num, entry_date, is_start)
    , constraint chk_maa check (country = 'MAA')
    , constraint chk_is_start check (is_start in (0, 1))  
    );
    
    
    begin
      -- Defined as block so you can remove / add
      delete from grks_trade_base;
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate - 1, 0);    -- 1. Test for end without start
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate    , 1);    -- 2. Consecutive starts (need to merge) 
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.1, 1);    -- 2.     "
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.2, 0);    -- 2. Consecutive ends (need to merge)
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.3, 0);    -- 2.     "
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.4, 1);    -- 3. New start
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.5, 0);    -- 3. New end
      insert into grks_trade_base(country, group_num, entry_date, is_start) values ('MAA', 1, sysdate+0.6, 1);    -- 3. Start without end, so end should be null
      commit;
    end;
    /
    
    select * from grks_trade_base;
    
    COUNTRY  GROUP_NUM ENTRY_DATE              IS_START
    ------- ---------- --------------------- ----------
    MAA              1 07/07/2022 02:29:59            0
    MAA              1 08/07/2022 02:29:59            1
    MAA              1 08/07/2022 04:53:59            1
    MAA              1 08/07/2022 07:17:59            0
    MAA              1 08/07/2022 09:41:59            0
    MAA              1 08/07/2022 12:05:59            1
    MAA              1 08/07/2022 14:29:59            0
    MAA              1 08/07/2022 16:53:59            1
    
    
    8 rows selected.
    
    create view grks_trade as  
      select * 
      from grks_trade_base
      match_recognize(
        partition by country, group_num
        order by entry_date
        measures first(A.entry_date) start_date, last(B.entry_date) end_date
        pattern(A+ B*)
        define
          A as is_start = 1
        , B as is_start = 0
      );
      
    select * from grks_trade;
    
    
    COUNTRY  GROUP_NUM START_DATE            END_DATE             
    ------- ---------- --------------------- ---------------------
    MAA              1 08/07/2022 02:29:59   08/07/2022 09:41:59  
    MAA              1 08/07/2022 12:05:59   08/07/2022 14:29:59  
    MAA              1 08/07/2022 16:53:59                        
    
    
    3 rows selected.
    
  • Stax
    Stax Member Posts: 130 Bronze Badge

    hi, Paulzip

    it is difficult to work with such a view

    imho, it is better to add a field end_date to the table

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Jul 8, 2022 10:07AM
  • Stax
    Stax Member Posts: 130 Bronze Badge

    data for a specific date is often required where :d between date_start and date_end

    judging by if((:NEW.START_DATE >= rec.START_DATE and :NEW.START_DATE <=rec.END_DATE),

    I don't quite understand what is needed

    clearly, we are talking about non-intersecting ranges, I agree with SY's decision

    1) block the resource (GROUP_NUM) and verify the condition

    2) create a mat view, checking the intersection by commit

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond

    @Paulzip: And how "For each row, simply capture a date and whether or not it is a start or end one" can be done without serializing in multi-session environment? And what happens when we delete row with is_start = 1? And how your solution ensures non-overlapping ranges?

    SY.

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Jul 8, 2022 1:47PM

    Stax :

    1) block the resource (GROUP_NUM) and verify the condition

    Try that in a busy system and see how well that works or scales

    Stax :

    2) create a mat view, checking the intersection by commit

    You'll need an MView log, MView fast commit, logic to check for overlaps (which will probably have to use match_recognize or fairly complex analytic query which might not be fast refreshable) on each commit.

    I'm still interested to see your proposed solution, rather than just saying "create a mat view, checking the intersection by commit"

    Solomon said:

    @Paulzip: And how "For each row, simply capture a date and whether or not it is a start or end one" can be done without serializing in multi-session environment? And what happens when we delete row with is_start = 1? And how your solution ensures non-overlapping ranges?

    Why do you think you need to serialize?

    If you delete a row with is_start=1, then any orphaned is_start = 0 are ignored. By design, consecutive matching is_start points are merged into intervals.

    A(IS_START=1)---------B(1)-------C(0)----D(0)

    1) Interval is AD

    2) Delete A => Interval becomes BD

    3) Delete D => Interval becomes BC

    4) If you want to...

    a) Delete the whole interval, use the view to find all points between A-D in the base table and delete

    b) Break an interval, insert a 0 and 1.