Re: Triggers Newbie

In article <xawB5.14216$O7.192119_at_ozemail.com.au>,   "Susan Entwisle" <sentwisle_at_ozemail.com.au> wrote:
> Hi,
>
> I am writing my first trigger and I have a problem with the exception
 clause
> (see below) , what I would like to do in the event of an exception
 write to
> a log table and inform the user. When this exception handler is
 executed
> the RAISE_APPLICATION_ERROR message is being displayed but there is no
> record being inserted into the log_info table. I believe this is
 occurring
> because the RAISE_APPLICATION_ERROR is causing the rollback to occur
 to the
> log_info table. As I cannot directly use the COMMIT statement (nor
 would I
> want to commit) in a trigger is there any other way that I can
 perform both
> of these tasks.
>
> Thanking you in advance.
>
> Cheers
> Susan
>
> EXCEPTION
>
> WHEN e_TimesEqual THEN
> /* Handler for when the commence times and end time are equal */
> INSERT INTO log_info (time_logged, machine_logged, code_id, info)
> VALUES (v_CurrentTimeDate, v_Terminal, -20000, 'Invalid Commence
 Time and
> End Time Equal for Packer' || :new.packer_id || 'from ' ||
> :new.commence_time || ' to ' || :new.end_time );
> RAISE_APPLICATION_ERROR(-20000, 'Invalid Commence Time and End Time
> Equal');
>

Raise_application_error causes all actions in the trigger to rollback to the insert. Since you can not commit or rollback in a trigger it is up to the calling program to issue a rollback, insert into the log_info table, and commit this error message insert.

This would be a nice place to be able to use an anonymous transaction of 8.1, but I do not believe that they can be imbedded in triggers or stored code. Our main systems are 8.0.5/6 so I have not been able to test this last.

If you have the Enterprise edition the Advanced Queing option, ver 8+, has the ability to accept anonymous messages so this would be one option, and a second option would be to use the dbms_pipe package and a daemon program as you system error log facility.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.