Forum Stats

  • 3,837,605 Users
  • 2,262,272 Discussions
  • 7,900,326 Comments

Discussions

Categories

gather records in compound trigger and process them

TelluriumDBA
TelluriumDBA Member Posts: 20 Red Ribbon
edited Jul 10, 2019 11:45AM in SQL & PL/SQL

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.

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited Jul 10, 2019 6:07AM

    > 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

    · Share on TwitterShare on Facebook
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Jul 10, 2019 8:42AM

    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 --

    · Share on TwitterShare on Facebook
  • cormaco
    cormaco Member Posts: 1,950 Silver Crown
    edited Jul 10, 2019 9:30AM

    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
    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Jul 10, 2019 10:19AM

    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

    · Share on TwitterShare on Facebook
  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited Jul 10, 2019 10:47AM

    > 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

    · Share on TwitterShare on Facebook
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Jul 10, 2019 11:26AM 
    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

    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Jul 10, 2019 11:45AM 
    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

    · Share on TwitterShare on Facebook