如何使用触发器在PL/SQL中将值从一个表插入到另一个表 [英] 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;
/

这是我要计算和存储来自student_report表的百分比和sid的另一个表.

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)

这是我尝试的代码:

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.

推荐答案

尝试使用

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

这篇关于如何使用触发器在PL/SQL中将值从一个表插入到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！