Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Trigger in mutating error while update

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;
Best 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.
Answers
-
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?
-
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
-
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.
-
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.
-
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.
-
hi, Paulzip
it is difficult to work with such a view
imho, it is better to add a field end_date to the table
-
Why is it?
Let's see your solution then.
-
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
-
@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.
-
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.