Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Can we do this: Populate a variable inside a trigger?

Re: Can we do this: Populate a variable inside a trigger?

From: Bjrn Drr Jensen <B.D.Jensen_at_gmx.net>
Date: Sat, 2 Sep 2006 00:24:45 +0200
Message-ID: <003b01c6ce15$6f6a7280$6601a8c0@LENO>


Hi!
Whats wrong with stored procedures?
You should be able to put your script in an package... /Bjoern

  CREATE OR REPLACE TRIGGER features_tbl_au AFTER UPDATE ON features_tbl REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE

      v_file VARCHAR2(1024);
  BEGIN

      IF (:NEW.enabled = 1 AND :OLD.enabled = 0) THEN
          v_file := '/vobs/NMS/nms/sql/nms_security/' || :NEW.name;
          @@v_file;
      END IF;
  EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
          NULL; -- script has already been run so just ignore this type of error 
      WHEN OTHERS THEN
          RAISE;

  END;
  /

  SP2-0310: unable to open file "v_file.sql"

  Is there a way to execute the script - the trigger will not pass the v_file variable to complete : NEW.name variable.

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 01 2006 - 17:24:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US