Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    7

    Question Unanswered: Trigger help - Please :)

    I am trying to write atrigger in Oracle and I need help.. ALOT probably...

    I have two tables, nv_poll and tecmail. When I enter 3 fields of data in Nv_poll table(hostname, rule_id and group_id), I need it to automatically put a value from tecmail for the same hostname.

    Here's the trigger so far and the DBMS_OUPUT does display the information I need, but the set command will not update the field.
    Thanks alot!!!!

    Create OR REPLACE TRIGGER Nv_poll_trg
    AFter INSERT or UPDATE 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 tecmail.mail_id%TYPE;
    BEGIN
    DBMS_OUTPUT.PUT_LINE (:new.mail_id);
    SELECT mail_id into v_info
    from tecmail
    where logid = v_logid
    and hostname = v_hostid;
    UPDATE NV_POLL
    SET MAIL_ID = v_info
    WHERE HOSTNAME = v_hostid;
    DBMS_OUTPUT.PUT_LINE (v_info);
    DBMS_OUTPUT.PUT_LINE (v_hostid);
    commit;
    END;
    /


    Other information that might be helpful
    ---------------------

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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Trigger help - Please :)

    To change the value of a field being inserted or updated requires a BEFORE trigger.

    And you don't UPDATE the triggering table, just set the :NEW value as required.

    The REFERENCING clause is pointless.

    You don't want / can't have an autonomous transaction and commit either - if your INSERT/UPDATE fails, so should any triggered actions.

    So your trigger becomes:

    CREATE OR REPLACE TRIGGER Nv_poll_trg
    BEFORE INSERT or UPDATE on NV_POLL
    FOR EACH ROW
    --WHEN (new.mail_id is null)
    BEGIN
    SELECT mail_id into :NEW.mail_id
    from tecmail
    where logid = 'NODEDWN'
    and hostname = :NEW.hostname;
    END;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •