CODEWITHSUNDEEP
sqloracle-databaseoracle11gtriggers
adsdassddad
adsdassddad

Reputation: 11

Oracle SQL Trigger errors - ORA-06512, ORA-04088

I've got the following trigger:

create or replace trigger CHILD_DOB_TRIGGER
before insert on CHILDREN
for each row
  begin
  if :NEW.CHILD_DOB > sysdate then
  raise_application_error(-20001, 'CHILD_DOB must be greater than current date');
  end if;
  end;
/

But if I try to insert:

INSERT INTO CHILDREN 
VALUES ((TO_DATE('2020/05/03', 'yyyy/mm/dd')));

I get the following errors:

SQL Error: ORA-20001: CHILD_DOB must be greater than current date
ORA-06512: at "USER.CHILD_DOB_TRIGGER", line 3
ORA-04088: error during execution of trigger 'USER.CHILD_DOB_TRIGGER'

Upvotes: 1

Views: 4260

Answers (2)

Petr Pribyl
Petr Pribyl

Reputation: 3575

There is nothing wrong with your trigger. It simply threw an exception ORA-20001 because of calling raise_application_error.

Oracle in such case throws more exceptions in a stack :

you get an information, that your INSERT statement failed

ORA-04088: error during execution of trigger 'USER.CHILD_DOB_TRIGGER'

next messege is where in your code exception appeared

ORA-06512: at "USER.CHILD_DOB_TRIGGER", line 3

and the last message shows main problem

ORA-20001: CHILD_DOB must be greater than current date

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

if :NEW.CHILD_DOB > sysdate then raise_application_error(-20001, 'CHILD_DOB must be greater than current date');

You are raising a user-defined exception if the new inserted date is greater than SYSDATE.

TO_DATE('2020/05/03', 'yyyy/mm/dd')

The above date is greater than SYSDATE, and thus your trigger does exactly what you asked it to do.

For example,

SQL> CREATE OR REPLACE TRIGGER CHILD_DOB_TRIGGER
  2  before insert on emp
  3  for each row
  4    BEGIN
  5    if :NEW.hiredate > sysdate then
  6    raise_application_error(-20001, 'CHILD_DOB must be greater than current date');
  7    end if;
  8    END;
  9  /

Trigger created.

SQL>
SQL> sho err
No errors.
SQL>
SQL> INSERT INTO emp(hiredate)
  2  VALUES ((TO_DATE('2020/05/03', 'yyyy/mm/dd')));
INSERT INTO emp(hiredate)
            *
ERROR at line 1:
ORA-20001: CHILD_DOB must be greater than current date
ORA-06512: at "LALIT.CHILD_DOB_TRIGGER", line 3
ORA-04088: error during execution of trigger 'LALIT.CHILD_DOB_TRIGGER'


SQL>

Upvotes: 0

Related Questions