CODEWITHSUNDEEP
sqloracle-databaseplsql
Darius
Darius

Reputation: 15

error at creating a trigger that is supposed to raise_application_error

Ok, so I try to run this code:

Create or replace trigger modificare_datar
After Insert or Update of datar
ON Imprumut
for each row

Begin

if datar<datai or datar>sysdate
then Raise_application_error(-20500,'Eroare! datar nevalida');
end if;

End;

But i get the next error:

Error at line 3: PL/SQL: Statement ignored


1. Create or replace trigger modificare_datar
2. After Insert or Update of datar
3. ON Imprumut
4. for each row
5. Begin

What i want to do is to create a trigger that raises an error because the date datar that I want to update, or at the insertion is smaller that another date (datai) or it is bigger than the sysdate. But I dont really know why it gets me and Error. If not, i would like insted of raising and error to set the datar to sysdate, but if I try that the trigger will create but i would get 2 errors when it tries to run( i found out that it is because it will go on a loop) Can you help me ?

EDIT:

Ok, I tried this code 

Create or replace trigger modificare_datar 
After Insert or Update of datar ON Imprumut 
for each row 
Begin 
    if :new.datar<:new.datai or :new.datar>sysdate then 
        Raise_application_error(-20500,'Eroare! datar nevalida'); 
    end if; 
End; 

and it lets me create it, but now i get the following error when the trigger fires 

ORA-20500: Eroare! datar nevalida ORA-06512: at "RO_A372_SQL_S20.MODIFICARE_DATAR", line 4 ORA-04088: error during execution of trigger 'RO_A372_SQL_S20.MODIFICARE_DATAR' 2. set datar='26-Nov-2018' 3. where id_carte=6 and id_imp=4

Upvotes: 1

Views: 1013

Answers (2)

Littlefoot
Littlefoot

Reputation: 142713

This might be doing what you're looking for. Have a look:

Sample table:

SQL> create table imprumut
  2    (id     number,
  3     datar  date,
  4     datai  date);

Table created.

Trigger: you forgot to specify which values you're referencing:

SQL> create or replace trigger trg_biu_imp
  2    before insert or update on imprumut
  3    for each row
  4  begin
  5    if :new.datar < :new.datai or :new.datar > sysdate then
  6       raise_application_error(-20500, 'Eroare! datar nevalida');
  7    end if;
  8  end;
  9  /

Trigger created.

Testing:

SQL> -- datar < datai: error
SQL> insert into imprumut (id, datar, datai) values
  2    (1, date '2018-11-30', date '2018-12-20');
insert into imprumut (id, datar, datai) values
            *
ERROR at line 1:
ORA-20500: Eroare! datar nevalida
ORA-06512: at "SCOTT.TRG_BIU_IMP", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_IMP'


SQL> -- datar > sysdate (2018-11-30): error
SQL> insert into imprumut (id, datar, datai) values
  2    (2, date '2020-01-01', date '2018-12-20');
insert into imprumut (id, datar, datai) values
            *
ERROR at line 1:
ORA-20500: Eroare! datar nevalida
ORA-06512: at "SCOTT.TRG_BIU_IMP", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_IMP'


SQL> -- datar > datai, datar < sysdate: OK
SQL> insert into imprumut (id, datar, datai) values
  2    (3, date '2018-11-01', date '2018-08-20');

1 row created.

SQL>

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116110

The first issue, as you already figured out, is that you need :new.datar (or :old.datar, depending on the situation) to get to the date value of the record you are inserting or updating.

The other issues of having additional errors, is actually not really an issue. The first one is the actual error, with the right code and the right text. The other part is extra information to help you determine more information about the error, if you would be interested in that. So you get ORA-06512, which is following the 'stack trace' to tell you on which line something went wrong. This can be of use particularly if you got more code, or if you are calling stored procedures from the trigger. Using this information, you can trace all the steps from the call you made to the place where the error was raised.

So all that extra information is not part of the actual error message. When you would use SQLCODE and SQLERRM you would get only the first number and the first message (the one you raised).

Upvotes: 1

Related Questions