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.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 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
- 475 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
gather records in compound trigger and process them

i have the following table. when a record is being inserted, i need to make sure the latest record of a given batch is updated as isLatest = 1. the update also should update the previous isLatest to 0 as well.
tableA
(
batchNumber number(10),
runNumber number(10),
serialNumber number(10),
isLatest number(1)
)
so i write a compound trigger
i need the trigger to be as follows:
after each row is
begin
<gather records which were inserted to objectA>
end
after statement is
select a.batchNumber, a.runNumber, a.serialNumber from tableA a inner join objectA o on a.batchNumber = o.batchNumber and a.isLatest = 0;
the above will be collected to an object (objectB).
now, i need to filterout the latest records. so i do this :
select batchNumber, runNumber, serialumber, row_number() over( partition by batchNumber order by runNumver desc, serialNumber desc) as LatestRank from objectB
the above will be collected to objectC
now, object C has to be updated with the TableA
merge into TableA ta using (select * from objectC where LatestRank <>1) R
on (ta.batchNumber = r.batchNumber and ta.runNumber = r.runNumber and ta.serialNumber = r.serialNumber)
when matched then
update set a.isLatest = 0;
===================================
i am confused as what method to use for the "objects". i tried both ref cursor and table type but no luck. Appreciate any help in this regard.
Answers
-
> i am confused as what method to use for the "objects". i tried both ref cursor and table type but no luck
View examples from the documentation:
Using Compound DML Triggers with Bulk Insertion
TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
salaries salaries_t;
Using Compound DML Triggers to Avoid Mutating-Table Error
TYPE Department_Salaries_t IS TABLE OF Employees.Salary%TYPE INDEX BY VARCHAR2(80);
Department_Avg_Salaries Department_Salaries_t;
https://docs.oracle.com/database/121/LNPLS/triggers.htm#LNPLS770
Regards,
Zlatko
-
TelluriumDBA, I obviously do not understand what you are doing within the trigger because all I can think of is Row Level trigger which means it fires once for each trigger action. If the logic is insert one row and mark it as last then on insert of the second row mark it as last and update the first row as not last then insert third row marking it as last and updating second to be not the last row inserted, etc.... that seems extremely inefficient. If the data is batched then why doesn't the input program just mark the last row with the '1'? Does the logic issues a single commit at the end of the batch or commit within the batch?
- -
As I said the problem could just be because I do not write code very often, but more explanation of the requirements might help. Posting the actual trigger code might prove necessary.
- -
HTH -- Mark D Powell --
-
You shouldn't store data in a table that can be calculated from other data, this breaks normal form.
Instead you could create a view with isLatest as a calculated column.
Here is an example:
create table tableA( batchNumber number(10), runNumber number(10), serialNumber number(10));delete from tableA;insert into tableA values(1,1,1);insert into tableA values(1,1,2);insert into tableA values(1,2,1);insert into tableA values(1,2,3);insert into tableA values(2,1,1);insert into tableA values(2,1,2);commit;create or replace view viewA as select t.*, case row_number() over( partition by batchNumber order by runNumber desc, serialNumber desc) when 1 then 1 else 0 end as isLatest from tableA t; select * from viewA order by batchNumber, runNumber ,serialNumber;
BATCHNUMBER RUNNUMBER SERIALNUMBER ISLATEST----------- ---------- ------------ ---------- 1 1 1 0 1 1 2 0 1 2 1 0 1 2 3 1 2 1 1 0 2 1 2 1
-
The OP definitely needs to change the design.
Something tells me you can't perform a DML against TableA within a trigger on TableA
MK
-
> Something tells me you can't perform a DML against TableA within a trigger on TableA
It is possible but it is (relatively) complex.
For example:
It is questionable whether this makes sense in this case (probably not).
Regards,
Zlatko
-
Mike Kutz wrote:The OP definitely needs to change the design.Something tells me you can't perform a DML against TableA within a trigger on TableAMK
You can in statement level triggers and in the statement level part of compound triggers.
EDIT: If I'd read the thread properly I would have noticed Zlatko pointed that out half an hour ago
-
Zlatko Sirotic wrote:> Something tells me you can't perform a DML against TableA within a trigger on TableAIt is possible but it is (relatively) complex.For example:Re: Recursive trigger solution It is questionable whether this makes sense in this case (probably not).Regards,Zlatko
IMHO - "(relatively) complex" === un-maintainable code.
Also - I like to write code that my Juniors (and, in the future, their Juniors) can maintain with minimal guidance (and maybe a little more experience).
that is: this solution won't pass my Code Review
My preferred solution would be "Use a VIEW and TAPI/XAPI"
By using a VIEW and TAPI/XAPI, the coding style fits the SmartDB design very nicely.
This design also seems like an MVC architectural pattern when you assume M (model) = the hidden tables, V (view) = the VIEW; C (controller) = the TAPI/XAPI
At worst, add an INSTEAD OF trigger to @cormaco's VIEW.
MK