I'm getting an error while excuting a trigger statement-CodePudding

enter image description here CREATE OR REPLACE TRIGGER weekd_tri

BEFORE INSERT OR UPDATE ON payment

FOR EACH ROW

BEGIN

IF TO_CHAR(SYSDATE, 'D') = '1' THEN

RAISE_APPLICATION_ERROR(-20000, 'Cannot make a payment on weekends');

END IF;

END;

error msg ORA-06512: at "SCOTT.WEEKD_TRI", line 3 ORA-04088: error during execution of trigger 'SCOTT,WEEKD_TRI can you tell me why i'm getting this error and how to solve this error

* *this trigger should not accept payment by customer when made on Sat/Sun so i wrote this trigger but when I stried to insert data on Sun it did Fire a triger ORA - 20000 Cannot make a payment on weekends and I was not able to update the values but apart from that I got the other two Erros ORA-06512 & ORA:04088 **

CodePudding user response：

There's no "error", except the one you created yourself, intentionally.

Which day is it today?

SQL> select to_char(sysdate, 'D') from dual;

T
-
1       --> OK, that's the value you're checking in a trigger

SQL>

Sample table:

SQL> create table payment (id number);

Table created.

Trigger:

SQL> create or replace trigger weekd_tri
  2    before insert or update on payment
  3    for each row
  4  begin
  5    if to_char(sysdate, 'D') = '1' then
  6       raise_application_error(-20000, 'Cannot make a payment on weekends');
  7    end if;
  8  end;
  9  /

Trigger created.

Testing:

SQL> insert into payment (id) values (1);
insert into payment (id) values (1)
            *
ERROR at line 1:
ORA-20000: Cannot make a payment on weekends
ORA-06512: at "SCOTT.WEEKD_TRI", line 3
ORA-04088: error during execution of trigger 'SCOTT.WEEKD_TRI'

Right; that's EXACTLY what you wanted to do: IF concluded that today really is '1' and raised an error.

If it were e.g. '2' you checked, today you'd be able to insert a row:

SQL> create or replace trigger weekd_tri
  2    before insert or update on payment
  3    for each row
  4  begin
  5    if to_char(sysdate, 'D') = '2' then
  6       raise_application_error(-20000, 'Cannot make a payment on weekends');
  7    end if;
  8  end;
  9  /

Trigger created.

SQL> insert into payment (id) values (2);

1 row created.

SQL>

CodePudding user response：

As from your screenshot I see there are 3 error message lines

ORA-20000 - which is the real error message that has been caused from your logic
ORA-6512 - service message to identify the line of code error occured
ORA-4088 - another service message to identify trigger name where error occured

So, the reason why you're getting ora-4088 is raise_application_error command you're using for exception firing. It is just a part of service message for developers