got error in SQL in my oracle when i call my trigger to update total in my STUDENT table

Question

I have to call a trigger which updates fields of total and percentage after query is inserted heres the code

create table bhrugus1 (
  student_name varchar2(100),
  dbms number,
  dsa number,
  mco number,
  total number,
  percentage number,
  roll_no number primary key

);


CREATE OR REPLACE TRIGGER trial1 
AFTER INSERT on bhrugus1
REFERENCING new AS new
FOR EACH ROW 
BEGIN
      :new.total := :new.dbms + :new.dsa + :new.mco; 
      :new.percentage := (:new.dbms + :new.dsa + :new.mco) / 3 ;
END;


insert into bhrugus1 values ('bhrugu',90,90,90,1,1,2);

SELECT * from bhrugus1;

the table and while running trigger i get error

the new error

Looks like this https://stackoverflow.com/questions/47584800/oracle-column-not-allowed-here. Change your "bhrugu" to 'bhrugu' — Craig, May 11 '21 at 06:05
[As documented in the manual](https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-language-fundamentals.html#GUID-356CB656-68ED-4869-8C67-FE93A78AEC9A) the assignment operator is `:=` in PL/SQL — , May 11 '21 at 06:20
i updated it now it says" cannot change NEW values for this trigger type" — 19ce141 BHRUGU SHARMA, May 11 '21 at 06:23

Kapitany · Accepted Answer · 2021-05-11T06:23:15.550

0

Please modify your trigger:

CREATE OR REPLACE TRIGGER trial1 
before INSERT on bhrugus1
REFERENCING new AS new
FOR EACH ROW 
BEGIN
      :new.total := :new.dbms + :new.dsa + :new.mco; 
      :new.percentage := (:new.dbms + :new.dsa + :new.mco) / 3 ;
END;

You shouldn't run an update statement in your trigger, use the :new... instead of update.

The other important thing is:

before insert

edited May 11 '21 at 06:23

answered May 11 '21 at 06:09

Kapitany

1,319
1
6
10

I modified the answer: the trigger is before insert and the assignment operator is :=, as a_horse_with_no_name said. – Kapitany May 11 '21 at 06:24
it solved the error, i did both that and BEFORE – 19ce141 BHRUGU SHARMA May 11 '21 at 06:28

score 0 · Answer 2 · answered May 12 '21 at 18:13

You are missing a component, what happens when someone UPDATEs any or all of dbms,dsa,mco. There is no update trigger to recalculate total or percentage. You could of course just include 'OR UPDATE' on the trigger. A better way however would be defining them a virtual columns.

create table bhrugus1 (
  student_name varchar2(100),
  dbms number,
  dsa number,
  mco number,
  total number generated always as (dbms+dsa+mco) virtual,
  percentage number generated always as ((dbms+dsa+mco)/3) virtual,
  roll_no number primary key
);

With that both total and percentage (bad name it is the average - but that is another issue) will always calculated properly. And drop the trigger it is no longer needed.

got error in SQL in my oracle when i call my trigger to update total in my STUDENT table

2 Answers2