Forum Stats

  • 3,837,697 Users
  • 2,262,286 Discussions
  • 7,900,365 Comments

Discussions

insert values using trigger

Marco Foxx
Marco Foxx Member Posts: 182 Blue Ribbon
edited Mar 23, 2018 8:11AM in SQL & PL/SQL

Data will be first inserted into temp_data(using oracle APEX DML process) after the insertion comlplete i need to get those records and insert into temp_head and temp_detail. In, temp_head i need to insert sum on RATE and sum of Total when ID is same and in temp_detail there will be line wise entry same as temp_data based on ID.

create table temp_head(id number,rate number,total number);create table temp_detail(id number,rate number,total number);create table temp_data (id number,rate number,total number);insert into temp_data(1001,100,100);insert into temp_data(1001,200,200);insert into temp_data(1001,300,300);insert into temp_data(1002,400,400);insert into temp_data(1002,500,500); commit;

TEMP_DATA

id     rate  total

1001   100   100

1001   200   200

1001   300   300

1002   400   400

1002   500   500

So, expected output of TEMP_HEAD for ID = 1001 and ID = 1002:

id     rate  total

1001   600   600

1002   900   900

TEMP_DETAIL

  id     rate  total

1001   100   100

1001   200   200

1001   300   300

1002   400   400

1002   500   500

is there any way i can accomplish this using trigger.

Tagged:
BEDE

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,114 Red Diamond
    edited Mar 23, 2018 7:45AM

    Why do you want a trigger for this?

    If you're using apex, then you just need to create another process to happen after the insertion(s) have taken place, and then apply whatever insert code you want in that.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,114 Red Diamond
    edited Mar 23, 2018 7:46AM

    p.s. there's also an space where you can get help if you are unsure how to achieve it in Apex.

  • Marco Foxx
    Marco Foxx Member Posts: 182 Blue Ribbon
    edited Mar 23, 2018 7:47AM

    Thanks for feed back. Please consider DML process as which APEX provides which is inbuilt process where by i don't have to code manually which is better that way only.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,833 Red Diamond
    edited Mar 23, 2018 7:49AM

    On insert/update/delete trigger on table temp_detail, adjust that row's ID totals in table temp_head.

    But the solution you are describing here sounds flawed. Why temp tables? Why duplicate rows from the data table into the detail table? What is the use case for the header table?

    BEDE
  • Marco Foxx
    Marco Foxx Member Posts: 182 Blue Ribbon
    edited Mar 23, 2018 7:54AM
    Billy~Verreynne wrote:On insert/update/delete trigger on table temp_detail, adjust that row's ID totals in table temp_head.But the solution you are describing here sounds flawed. Why temp tables? Why duplicate rows from the data table into the detail table? What is the use case for the header table?

    - temp tables are just naming convection so can create a show case to put here.

    - header table will further be processed for other operations as well as detail table.

    - its not duplicate data every table will be used for different purposes.

    Thank you

  • BluShadow
    BluShadow Member, Moderator Posts: 42,114 Red Diamond
    edited Mar 23, 2018 8:11AM
    Marco Foxx wrote:Thanks for feed back. Please consider DML process as which APEX provides which is inbuilt process where by i don't have to code manually which is better that way only. 

    Sorry, that doesn't make sense.  Apex is meant to be coded just as much as we write code on the database.

    If you must, then write a procedure on the database and have a post insertion process in Apex that calls that.

    Triggers are not the correct place to be analysing and doing DML that isn't solely related to the table that the trigger is on.

This discussion has been closed.