Re: Trigger help - Please

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);

I think there are several things here that are preventing you from succeeding.
Are you getting an run-time error telling that you cannot reference "new" and "old" fields perhaps?
If I am not wrong, you cannot change the values of the fields in "AFTER" triggers. I think you have to change the declaration of the trigger and make it a BEFORE trigger (dropping this one first). Also, unless there is some special reason to do that, why are you using an autonomous transaction?
Is this a logging or auditing trigger perhaps? What is the expected behaviour if a rollback is issued, should the record content be backed out or not?
Why are you doing that weird declaration of OLD and NEW tags? It's useless.

Last but not least, you cannot update the table whose trigger is fired off, not in this fashion. You have been working with Sql Server, haven't you?
You should assing the value to the variable you declared for the task:

:new.mail_id := v_info;

Also, you may want to write that in a single SQL statement selecting the value into the :new.mail_id. Temporary variables storing :new and :old values are absolutely redundant and confusing unless you need to change their value at some stage.
Finally, why did you put the v_info between tick marks? Don't you want to assign its value to the :new.mail_id field ?

Bye,
Flavio Received on Wed Nov 27 2002 - 14:50:15 CET