Trigger help - Please

I am trying to implement a trigger with little to no success. When I add 3 fields of a row in NV_POLL table, I want a trigger to fire to lookup the 'MAIL_ID' from another table and insert it into the 4th field in the NV_POLL table.

The Table information is below this code, I'd really appreciate any advice you can lend. (perhaps there is even a better way to do what I am trying?) Thanks

Create OR REPLACE TRIGGER Nv_poll_trg
AFter INSERT on NV_POLL
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (new.mail_id is null)
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;

v_hostid varchar2(64) := :new.hostname;
v_logid tecmail.LOGID%TYPE := 'NODEDWN';
v_info varchar2(20);

       where logid = v_logid
       and hostname = v_hostid;

DROP TABLE NV_Poll CASCADE CONSTRAINTS;
CREATE TABLE NV_Poll (

       HostName VARCHAR2(64) NOT NULL,
       Rule_ID VARCHAR2(16) NOT NULL,
       Mail_ID VARCHAR2(128),
       Group_ID VARCHAR2(64) NOT NULL

       Mail_ID    VARCHAR2(128) NOT NULL,
       HostName   VARCHAR2(64) NOT NULL,
       LogID      VARCHAR2(64) NOT NULL,
       NotifyAddress VARCHAR2(128) NOT NULL

       ADD (PRIMARY KEY (HostName) ) ;

ALTER TABLE TecMail

       ADD (PRIMARY KEY (Mail_ID) ) ;
ALTER TABLE NV_POLL

        ADD CONSTRAINT fk_mailid_01 foreign key (mail_id)
        references tecmail (mail_id);

--
Posted via http://dbforums.com