CODEWITHSUNDEEP
plsql
ARIJIT DASGUPTA
ARIJIT DASGUPTA

Reputation: 13

How to insert values from one table to another in PL/SQL using trigger

I have created a trigger for a table but I want to calculate percentage and insert it into another table, for that I need to create another trigger this is the the trigger from where I'm getting the total value:

CREATE OR REPLACE TRIGGER stud_rep
BEFORE DELETE OR INSERT OR UPDATE ON student_report
FOR EACH ROW
BEGIN
:new.total := :new.sub1 + :new.sub2 + :new.sub3;
END;
/

and this is the another table where I want to calculate and store the percentage and sid from student_report table.

SQL> DESC students_percentage;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SID                                                NUMBER(3)
 TOTAL_PER                                          NUMBER(2)

and this is the code I tried:

SQL> CREATE TRIGGER update_percentage
  2  ON item
  3  AFTER UPDATE,INSERT
  4  AS
  5  BEGIN
  6  INSERT INTO students_percentage VALUES(SELECT sid, total FROM student_report);
  7  END;
  8  /
ON item
*
ERROR at line 2:
ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword


SQL> SHOW ERROR;
No errors.

Upvotes: 0

Views: 546

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

Try using 

CREATE TRIGGER update_percentage
AFTER INSERT
ON item FOR EACH ROW
BEGIN
INSERT INTO students_percentage (SELECT sid, total FROM student_report);
END;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

Code that does something might look like this; first, test case:

SQL> create table item (sid number);

Table created.

SQL> create table student_report(sid number, total number, sub1 number, sub2 number, sub3 number);

Table created.

SQL> create table students_percentage (sid number, total_per number);

Table created.

SQL>

Triggers:

SQL> create or replace trigger trg_stud_rep
  2    before delete or insert or update on student_report
  3    for each row
  4  begin
  5    :new.total := :new.sub1 + :new.sub2 + :new.sub3;
  6  end;
  7  /

Trigger created.

SQL> create or replace trigger trg_update_percentage
  2    after update or insert on item
  3    for each row
  4  begin
  5    insert into students_percentage (sid, total_per)
  6      select sid, total from student_report
  7      where sid = :new.sid;
  8  end;
  9  /

Trigger created.

SQL>

Testing:

SQL> insert into student_report (sid, sub1, sub2, sub3) values (100, 1, 2, 3);

1 row created.

SQL> insert into item (sid) values (100);

1 row created.

SQL> select * From item;

       SID
----------
       100

SQL> select * From student_report;

       SID      TOTAL       SUB1       SUB2       SUB3
---------- ---------- ---------- ---------- ----------
       100          6          1          2          3

SQL> select * From students_percentage;

       SID  TOTAL_PER
---------- ----------
       100          6

SQL>

On the other hand, I'd suggest you NOT to do it that way. Don't store any totals because they are expensive to maintain. For example, trigger on student_report table (I named it trg_stud_rep) and fires before DELETE won't do anything; there aren't any :new values, only :old ones, so you'll have to take care about it if you want to have accurate total value. Moreover, you're inserting it into yet another table.

Using triggers is tricky; they hide business logic and - when something goes wrong - you have to remember that you put some code into triggers as well (not only into your Forms application, stored procedures, whatever). Besides, if you load a lot of data, they'll fire for each row and slow everything down.

Simply, don't do that. Calculate totals when you need them, and that's most probably during presentation phase (i.e. in some report).

Use triggers for logging purposes, handle with care for everything else.

Upvotes: 1

Related Questions