Re: Trigger help - Please

Hi,

If I understand what you want to do, your trigger could be :

Create OR REPLACE TRIGGER Nv_poll_trg
BEFORE INSERT on NV_POLL
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (new.mail_id is null)
DECLARE
v_info varchar2(20);
BEGIN

	 SELECT mail_id
	 into :new.Mail_ID 
	 from tecmail
	 where logid = 'NODEDWN'
	 and hostname = :new.hostname;

You have to add an EXCEPTION to the SELECT to prevent the NO DATA FOUND error.

Dias

backer <member_at_dbforums.com> wrote in message news:<2090720.1038348673_at_dbforums.com>...
> 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);
> Cursor c_nvpoll IS
> SELECT mail_id from tecmail
> where logid = v_logid
> and hostname = v_hostid;
> BEGIN
> DBMS_OUTPUT.PUT_LINE (v_info);
> DBMS_OUTPUT.PUT_LINE (v_hostid);
> open c_nvpoll;
> fetch c_nvpoll into v_info;
> update nv_poll
> set mail_id = 'v_info' where hostname = 'v_hostid';
> commit;
> close c_nvpoll;
> END;
> /
>
>
>
> 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
> );
> DROP TABLE TecMail CASCADE CONSTRAINTS;
> CREATE TABLE TecMail (
> Mail_ID VARCHAR2(128) NOT NULL,
> HostName VARCHAR2(64) NOT NULL,
> LogID VARCHAR2(64) NOT NULL,
> NotifyAddress VARCHAR2(128) NOT NULL
> );
> ALTER TABLE NV_Poll
> 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);
Received on Sun Dec 01 2002 - 11:58:14 CET