Set up trigger to get sum of all values in a column-CodePudding

I am creating a trigger in SQL to sum up all the values in a column after a change is made. I am stuck and encountering an error when I try this: `

CREATE OR REPLACE TRIGGER GET_NUM_ATHLETES 
AFTER DELETE OR UPDATE OF NUM_ATHLETES OR INSERT ON DELEGATION 
BEGIN
  SELECT
    SUM("A1"."NUM_") "SUM(NUM_)"
 INTO x_1 FROM
    "DBF19"."DELEGATION" "A1";
END;

` My table looks like this:

ID	Num_
ABC	2
XYZ	4

I just used the Oracle SQL Developer GUI to create, but obviously doing something wrong.

CodePudding user response：

You could use a view instead of maintaining the data in a table. That way the view would get the results "live" each time.

And also you wouldn't need to do the extra task of loading data into another table

CREATE VIEW NUM_ATHLETES
AS
  SELECT SUM("A1"."NUM_") "SUM(NUM_)"
    FROM "DBF19"."DELEGATION" "A1";

CodePudding user response：

Do not create a table, use a VIEW (or if you were doing more complicated calculations a MATERIALIZED VIEW):

DROP TABLE num_athletes;

then:

CREATE VIEW num_athletes (id, num) AS
  SELECT id, SUM(num_)
  FROM   DBF19.DELEGATION
  GROUP BY id;