Re: care and feeding of triggers

Congratulations, you seem to have inherited a fine mess!

First of all: There is no need to have three triggers and they can be *very* easily replaced by one.
Here's how

CREATE or replace TRIGGER write_alarms_on_aiud after INSERT or update or delete ON alarm
 FOR EACH ROW
declare
action varchar2(16);
node_type alarm.node_type%rowtype;
if inserting then

   action := 'INSERT';
   node_type := :new.node_type;
elseif updating then

   action := 'UPDATE';
   node_type := :new.node_type;
else

   action := 'DELETE';
   node_type := :old.node_type;

write_alarm(action, .... , node_type);

You should replace the system call by the Oracle provided utl_file package. You'll need to set the utl_file_dir init.ora parameter to be able to use that.

Hth,

Sybrand Bakker, Oracle DBA

 write_alarms ( "INSERT", <30 cols snipped> new.node_type ))

<tony_barratt_at_my-deja.com> wrote in message news:8ri909$9pt$1_at_nnrp1.deja.com...
> Hi List,
>
> I have inherited some PL/SQL code that is intended to monitor an alarm
> table. Unsurprisingly, this alarm table has alarms, part of a fault
> managemnt system, inserted, updated and deleted.
> The plan is write these insertions, updates and deletions to a logfile,
> which can then be tailed thereby enabling near-realtime notification of
> any alarms on a remote machine.
> OK so far?
> The current approach is to create 3 triggers, insert, update and
> delete, which call a PL/SQL procedure, with the alarm data pased in as
> parameters, which makes a system call, which appends to a logfile.
> Phew!
> Here's the procedure:
> CREATE OR UPDATE PROCEDURE write_alarms ( status CHAR(6),
> <30 columns snipped>
> ,node_type INTEGER)
> SYSTEM 'echo '|| status ||': <30 columns snipped>
> || node_type ||': >> /var/log/alarms.log';
> END PROCEDURE;
>
> here's the triggers:
> CREATE TRIGGER write_alarms_on_i INSERT ON alarm
> REFERENCING NEW as new
> FOR EACH ROW(
> EXECUTE PROCEDURE write_alarms ( "INSERT", <30 cols snipped>
> new.node_type ));
>
> CREATE TRIGGER write_alarms_on_d DELETE ON alarm
> REFERENCING OLD as new
> FOR EACH ROW(
> EXECUTE PROCEDURE write_alarms ( "DELETE", <30 cols snipped>
> new.node_type ));
>
> CREATE TRIGGER write_alarms_on_u UPDATE ON alarm
> REFERENCING NEW as new
> FOR EACH ROW(
> EXECUTE PROCEDURE write_alarms ( "UPDATE", <30 cols snipped>
> new.node_type ));
>
> So that's what we've got.
> But I was wondering, do we need 3 triggers? can we do CREATE TRIGGER
> write_alarms_on_change ON UPDATE OR DELETE OR INSERT nad work some
> magic to put "UPDATE", "DELETE" or "INSERT" in the status field.
> Is using SYSTEM 'echo <snip> : >> /var/log/alarms.log';
> OK or is there a better way (using Oracle 7.3.3).
> Should I do some exception/error handling?
> Any other improvements?
>
> Thanx in advance,
>
> Tony
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Oct 05 2000 - 11:53:19 CDT