care and feeding of triggers

From: <tony_barratt_at_my-deja.com>
Date: Wed, 11 Oct 2000 18:09:38 GMT
Message-ID: <8s2acq$lii$1@nnrp1.deja.com>

Hi List,
I'm still struggling with a procedure and a trigger maybe someone can put me, and my code, right..

I've created a table alarm on a Oracle 7.3 db like so: SQL> describe hnmadm.alarm

 Name                            Null?    Type
 ------------------------------- -------- ----
 TABLE_ENUM                      NOT NULL NUMBER(38)
 AGENT_ID                        NOT NULL NUMBER(38)


<snip>

 NODE_TYPE                       NOT NULL NUMBER(38)

created a procedure:
hnmtf[hnmadm]/opt/hnmadm/sql% cat make_proc.sql CREATE OR REPLACE PROCEDURE write_alarms_i ( status VARCHAR2, agent_id INTEGER <snip>node_type INTEGER)
IS

        logmsg varchar2(1024);
        config_file UTL_FILE.FILE_TYPE;
BEGIN
        config_file := UTL_FILE.FOPEN ('/var/log', 'alarms.log', 'W');
        logmsg := status ||':'|| agent_id ||':'<snip>'|| node_type ;
        UTL_FILE.PUT_LINE (config_file, logmsg);
        UTL_FILE.FCLOSE (config_file);

END;
/
which compiles OK, and apart from the lack of exception handling seems OK (?)

created a trigger with make_trigger.sql: CREATE OR REPLACE TRIGGER write_alarms_on_i BEFORE INSERT or UPDATE ON hnmadm.alarm
FOR EACH ROW
DECLARE
action varchar2(6);
node_type varchar2(6);
BEGIN
IF inserting then

        action := 'INSERT';
        node_type := :new.node_type;
ELSIF updating then
        action := 'UPDATE';
        node_type := :new.node_type;

END IF; hnmadm.write_alarms_on_i ( action , new.agent_id, <snip> ,new.node_type );
END;
/

Failed to compile it:
SQL> @make_trigger
Warning: Trigger created with compilation errors. SQL> sho err
No errors.

First question, why does sho err not work? There seems to be something in user_errors:
select * from user_errors;
<snip>

WRITE_ALARMS_ON_I              TRIGGER               1
12         37
PLS-00201: identifier 'NEW.AGENT_ID' must be declared
WRITE_ALARMS_ON_I              TRIGGER               2
12          1

PL/SQL: Statement ignored
20 rows selected.

I sort of thought that agent_id would be visible cos it's in the alarm table, but maybe not?

TIA tony

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 11 2000 - 13:09:38 CDT