Trigger is not working when I'm trying to insert a value into a table

Question

I am trying to make an Insert Trigger for a table called Marks which has id, id_student, id_course, value, data_notation, created_at, updated_at.

I need to make an Update on the old value, if the value I want to insert is higher than the one already exists in the column, and if there are no values in the column you would do an Insert with the new value.

I created the Trigger and there are no compilation errors.

CREATE OR REPLACE TRIGGER insert_value
before INSERT ON Marks
FOR EACH ROW

BEGIN

   IF (:OLD.value IS NULL) THEN 
     DBMS_OUTPUT.PUT_LINE('Inserting.. because value is null');
     UPDATE Marks SET value = :NEW.value where id_student = :NEW.id_student;

   ELSE 
     DBMS_OUTPUT.PUT_LINE('Updating old value.. if old value is smaller than the one we want');

     IF (:OLD.value < :NEW.value) THEN
       UPDATE Marks SET value = :NEW.value where :OLD.id_student = :NEW.id_student;
     END IF;  
   END IF;

END;

I want to change the old value from an existing value 5 to null for a specific id.

update Marks set value = null where id = 692;
select * from Marks where id = 692;

But when I'm trying to insert a value into the table so I can change the value null into 6 via the trigger

INSERT INTO Marks
VALUES (692, 43, 12, 6,  '13-02-2018', '13-02-2018', '13-02-2018');

I am receiving an error.

Error report -
SQL Error: ORA-00001: unique constraint (STUDENT.SYS_C007784) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

And it prints one time:

Inserting.. because value is null

But when I'm trying to check if the trigger did its job, using:

SELECT * from Marks where id = 692;

It doesn't update anything.

It has to be a trigger triggered by an insert operation. So I can't make the insert into the table, but how else should I write it so it works?

Answer 1

You problem comes from recursive calling the trigger due to the insert. The following would work. It does not catch update statements. It only cares for inserts. If the row exists already the row gets deleted first and the existing value is used for the insert if the existing value is higher.

set lin 20000
drop table marks;

create table Marks(
  id          number,
  id_student  number,
  id_course   number,
  value       number,
  data_notation varchar2(40), 
  created_at  timestamp,
  updated_at  timestamp,
  CONSTRAINT marks#u UNIQUE (id, id_student, id_course)
  );

create or replace trigger mark_trigger
before insert on marks
for each row
declare
  l_value number;
  l_data_notation varchar2(40);
  l_created_at timestamp;
begin
  select value, data_notation, created_at
  into   l_value, l_data_notation, l_created_at
  from
  (select *
  from   marks
  where  marks.id   = :new.id
    and  marks.id_student = :new.id_student 
    and  marks.id_course  = :new.id_course
  order by created_at desc)    
    where  rownum=1;
  if l_value is null then
    return;
  end if;
  if l_value > :new.value then
    :new.value := l_value;
    :new.data_notation := l_data_notation; 
    :new.created_at := l_created_at;
  else
    :new.updated_at := systimestamp;
  end if;
  delete from marks 
  where marks.id  = :new.id
  and  id_student = :new.id_student 
  and  id_course  = :new.id_course;
exception
  when no_data_found then
    null;
end;  


create or replace procedure marks_insert(
  i_id          number,
  i_id_student  number,
  i_id_course   number,
  i_value       number,
  i_data_notation varchar2
)
is
begin
   INSERT INTO marks
        VALUES (i_id, i_id_student, i_id_course, i_value, i_data_notation, systimestamp, null);
END marks_insert;

begin
  delete from marks;
  marks_insert(1,1,1,5,'1 first entry');
  marks_insert(1,1,1,6,'1 second entry');
  marks_insert(1,1,2,3,'2 first entry');
  marks_insert(1,1,2,2,'2 second entry');
end;

select * from marks;

Output:

Table dropped.
Table created.
Trigger created.
Procedure created.
 PL/SQL procedure successfully completed.

        ID ID_STUDENT  ID_COURSE      VALUE DATA_NOTATION                            CREATED_AT                                         UPDATED_AT                                        
---------- ---------- ---------- ---------- ---------------------------------------- -------------------------------------------------- --------------------------------------------------
         1          1          1          6 1 second entry                           07/05/2019 13:31:31.266817                         07/05/2019 13:31:31.266928                        
         1          1          2          3 2 first entry                            07/05/2019 13:31:31.268032                                                                           

2 rows selected.

Answer 2

You are inserting into the Marks when you insert into the Marks (the insert statement in the trigger before inserting) and so on in a recursive way. Hence the direct cause of error.