TRIGGER for validating data-CodePudding

I have 2 tables:

Chestionar {
id_c pk
punctaj_max
}

Test{
id_t pk
punctaj
id_c fk
}

I want to define a trigger to validate that, before an update, the modified punctaj is between 0 and the punctaj_max from the chestionar table with that id_c.

I tried this but it doesn't work

CREATE OR REPLACE TRIGGER check_val_salary
BEFORE UPDATE of punctaj ON test
FOR EACH ROW
BEGIN

IF  :new.punctaj<0 OR :new.punctaj > (Select punctaj_max from chestionar c where c.id_c=:old.id_c)
THEN
RAISE_APPLICATION_ERROR (-20508, 'Punctaj out of bounds');

END;

Any tips please?

CodePudding user response：

Tables and some sample data for chestionar:

SQL> create table chestionar
  2    (id_c        number primary key,
  3     punctaj_max number);

Table created.

SQL> insert into chestionar
  2    select 1 id_c, 100 punctaj_max from dual union all
  3    select 2     , 200             from dual;

2 rows created.

SQL> create table test
  2    (id_t     number primary key,
  3     punctaj  number,
  4     id_c     number references chestionar(id_c));

Table created.

Trigger:

SQL> create or replace trigger trg_biu_test
  2    before insert or update on test
  3    for each row
  4  declare
  5    l_punctaj_max chestionar.punctaj_max%type;
  6  begin
  7    select c.punctaj_max
  8      into l_punctaj_max
  9      from chestionar c
 10      where c.id_c = :new.id_c;
 11
 12    if :new.punctaj not between 0 and l_punctaj_max then
 13       raise_application_error(-20000, 'Punctaj should be between 0 and ' || l_punctaj_max);
 14    end if;
 15  exception
 16    when no_data_found then
 17      raise_application_error(-20001, 'Can not find boundary for that ID');
 18  end;
 19  /

Trigger created.

Testing:

SQL> insert into test (id_t, punctaj, id_c) values (1001, 555, 3);
insert into test (id_t, punctaj, id_c) values (1001, 555, 3)
            *
ERROR at line 1:
ORA-20001: Can not find boundary for that ID
ORA-06512: at "SCOTT.TRG_BIU_TEST", line 14
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_TEST'


SQL> insert into test (id_t, punctaj, id_c) values (1001, 555, 1);
insert into test (id_t, punctaj, id_c) values (1001, 555, 1)
            *
ERROR at line 1:
ORA-20000: Punctaj should be between 0 and 100
ORA-06512: at "SCOTT.TRG_BIU_TEST", line 10
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_TEST'


SQL> insert into test (id_t, punctaj, id_c) values (1001,  55, 1);

1 row created.

SQL>