care and feeding of triggers

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:07:43 CDT