SQL & PL/SQL

Hi friends,

I have the following trigger which is responsible to insert values in a table. I have noticed that in some cases inserts are failing when this trigger executes. Is it possible to do a retry to insert in the same values if the trigger fails the first time. Can anyone suggest a logic to be included in this trigger to retry insert if it fails the first time._

The trigger code is as follows:

create or replace
trigger "MH_SHELLAU".SITE_JOURNAL_VIEW_INS_TRI
INSTEAD OF INSERT ON SITE_JOURNAL
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
v_stmt_str VARCHAR2(2000);
tablename VARCHAR2(200);
BEGIN
SELECT 'SITE_JOURNAL_' || to_char(sysdate, 'MM') INTO tablename FROM dual;
EXECUTE IMMEDIATE 'INSERT INTO ' || tablename || '(gen_date, region_code, company_code, msg_event, msg_class, message,seq)
VALUES (SYSDATE,'
|| ''''||:new.region_code || ''','
|| ''''||:new.company_code || ''','
|| ''''||:new.msg_event || ''','
|| ''''||:new.msg_class || ''','
|| ''''||:new.message || ''','
|| :new.seq
|| ')';

EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR(
num=> -20107,
msg=> 'Duplicate journal');
END site_journal_view_insert;


The SQL to create the table is as follows:_+

--------------------------------------------------------
-- DDL for Table SITE_JOURNAL_06
--------------------------------------------------------

CREATE TABLE "SITE_JOURNAL_06"
( "GEN_DATE" TIMESTAMP (6),
"REGION_CODE" VARCHAR2(5),
"PRINT_DATE" DATE,
"COMPANY_CODE" VARCHAR2(16),
"MSG_EVENT" VARCHAR2(10),
"MSG_CLASS" VARCHAR2(10),
"MESSAGE" VARCHAR2(512),
"SEQ" NUMBER(9,0),
"JNL_CAT" VARCHAR2(6)
)
/
--------------------------------------------------------
-- DDL for Index PK_SITE_JOURNAL_06
--------------------------------------------------------

CREATE UNIQUE INDEX "PK_SITE_JOURNAL_06" ON "SITE_JOURNAL_06" ("SEQ")
/
--------------------------------------------------------
-- Constraints for Table SITE_JOURNAL_06
--------------------------------------------------------

ALTER TABLE "SITE_JOURNAL_06" ADD CONSTRAINT "PK_SITE_JOURNAL_06" PRIMARY KEY ("SEQ") ENABLE
/