raise an exception

[sukimac]

Hi,

I've created an exception that throws a custom error message when an integrity constraint Ora message is thrown.
I'm trying to create a trigger that throws the exception (and thats all the trigger does) -

CREATE OR REPLACE TRIGGER XR1_TEST.MY_ERROR_TEST
BEFORE INSERT
ON XR1_TEST.CS_TEAM_MEMBER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

constraint_violation exception;
pragma exception_init(constraint_violation, -02291);

BEGIN
if :new.user_type_cd != 1 then null;
end if;
exception when constraint_violation then
DBMS_OUTPUT.PUT_LINE
('Care recipient doesn''t exist.');
RAISE;
end;


However, this doesn't work b/c the exception never gets raise? Any suggestions on how I can accomplish this? Thanks.

[marist89]

You're never raising the exception. For example:

constraint_violation exception; 
pragma exception_init(constraint_violation, -02291); 

BEGIN 
  if :new.user_type_cd != 1 then 
     raise constraint_violation; 
  end if; 
  exception 
    when constraint_violation then 
    DBMS_OUTPUT.PUT_LINE ('Care recipient doesn''t exist.'); 
end;

[sukimac]

thx marist89 for the suggestion - but it still doesn't prevent the Ora-02291 from being thrown.

CREATE OR REPLACE TRIGGER XR1_TEST.MY_ERROR_TEST
BEFORE INSERT
ON XR1_TEST.CS_TEAM_MEMBER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

constraint_violation exception;
pragma exception_init(constraint_violation, -02291);

BEGIN
if :new.user_type_cd = 1 then
raise constraint_violation;
end if;
exception when constraint_violation then
DBMS_OUTPUT.PUT_LINE
('Care recipient doesn''t exist.');
end;


SQL> insert into cs_team_member(team_member_id, admin_user_id, name, user_type_cd, care_file_id)
VALUES (2, 2, 'ERROR', 1, 10000);
2 insert into cs_team_member(team_member_id, admin_user_id, name, user_type_cd, care_file_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (XR1_TEST.CS_TEAM_MEMBER_FK1) violated - parent
key not found

[akkerend]

Seems the trigger is not fired, integrity constraints are evaluated before the trigger gets fired.

What exactly do you want? With DBMS_OUTPUT in a trigger the message is only displayed if serveroutput is set on.

[sukimac]

what I'm trying to accomplish is to generate my own error message a constraint violation is thrown. I know how to accomplish this in a stored procedure, but, our sql calls from java are direct not through stored procedures or packages. So, I need to have a trigger handle the error. I'd like to still have the constraint defined on the table. Thanks for any suggestions.

[jmodic]

Inside your exception handler use RAISE_APLICATION_ERROR which will replace the standard message with your own. So your exception handler should look something like:

exception when constraint_violation then
  -- dbms_output message will only be put into dbms_output buffer
  DBMS_OUTPUT.PUT_LINE  ('Care recipient doesn''t exist.');
  -- The following message will be put in the error stack and passed to the application
  RAISE_APPLICATION_ERROR(-20001,'Err: Care recipient doesn''t exist.');
end;

[akkerend]

The problem is, the trigger does not get executed.
Is it possible to handle the exception in the application? Or do not use a foreign key constraint, but create a trigger to check the integrity with your own error message.

[sukimac]

i've got it working with raise_application_error but that will cause the

ORA-06512: at "XR1_TEST.MY_ERROR_TEST", line 13
ORA-04088: error during execution of trigger 'XR1_TEST.MY_ERROR_TEST'

error messages - I'm trying to eliminate the ora thrown messages

it works like a charm in a stored procedure - maybe it can't be down in a trigger?

Create or Replace Procedure XR1_TEST.MY_ERROR_TEST(team_member_id_in IN NUMBER, admin_user_id_in IN NUMBER,
name_in IN VARCHAR2, user_type_cd_in IN NUMBER, care_file_id_in IN NUMBER)
IS
constraint_violation exception;
pragma exception_init(constraint_violation, -02291);

BEGIN

insert into cs_team_member (team_member_id, admin_user_id, name, user_type_cd, care_file_id)
VALUES (team_member_id_in, admin_user_id_in, name_in, user_type_cd_in, care_file_id_in);
EXCEPTION
WHEN constraint_violation THEN
DBMS_OUTPUT.PUT_LINE
('Care file doesn''t exist!');
end;