Hi, I am trying to create a trigger, which automatically calculates col1 col2 and inserts the sum-CodePudding

I am trying to create a trigger, which automatically calculates col1 col2 and inserts the sum into col3 after the data insertion. For some reason there is an error, Does anyone know how to achieve this?

CREATE OR REPLACE TRIGGER test_tr
AFTER INSERT ON test
FOR EACH ROW
DECLARE

test_one NUMBER(10);
test_two NUMBER(10);

BEGIN

SELECT random
INTO test_one
FROM test
WHERE id = id;

SELECT random2
INTO test_two
FROM test
WHERE id = id;

INSERT INTO test(test3) values ((test_one test_two));


EXCEPTION
  WHEN NO_DATA_FOUND THEN
        NULL;
END;

CodePudding user response：

Don't select from the same table as trigger won't see it; table is mutating. Use pseudorecords.

SQL> create table test_one (random number, random2 number, test3 number);

Table created.

SQL> create or replace trigger test_tr
  2    before insert on test_one
  3    for each row
  4  begin
  5    :new.test3 := :new.random   :new.random2;
  6  end;
  7  /

Trigger created.

SQL> insert into test_One (random, random2)
  2  select 1, 2 from dual union all
  3  select 5, 3 from dual;

2 rows created.

SQL> select * from test_One;

    RANDOM    RANDOM2      TEST3
---------- ---------- ----------
         1          2          3
         5          3          8

SQL>

On the other hand, why bother with a trigger? Create a virtual column:

SQL> create table test_one
  2    (random     number,
  3     random2    number,
  4     test3      number as (random   random2) virtual
  5    );

Table created.

SQL> insert into test_One (random, random2) values (3, 9);

1 row created.

SQL> select * from test_one;

    RANDOM    RANDOM2      TEST3
---------- ---------- ----------
         3          9         12

SQL>

Or, don't create anything and calculate random random2 when you need it.

CodePudding user response：

Don't use a trigger, from Oracle 11gR1, you can use a virtual column:

ALTER TABLE test ADD test3 NUMBER GENERATED ALWAYS AS (test1   test2);

Alternatively, when you want to display the test3 value then calculate it in the SELECT clause:

SELECT test1, test2, test1   test2 AS test3 FROM test;

db<>fiddle here