Home » SQL & PL/SQL » SQL & PL/SQL » trigger help
trigger help [message #229996] Tue, 10 April 2007 08:49
CWISE
Messages: 12
Registered: March 2003
Junior Member
I am using Oracle 9i. I tried to take a lot of the other fields out of this trigger so that the real problem could be seen. I am trying to create a trigger that cleans up a string field, then verifies the new character with a field in another table. Once they are equal it will grab that ID number and place it in required field. Below is what I have so far for the trigger.

Table 1
SP_id INTEGER
TT_id INTEGER
LEGACY VARCHAR2

Table 2
SID NUMBER (10)
UID VARCHAR2
DESCRIPTION VARCHAR2

Table 3
T_id INTEGER
NAME VARCHAR2



CREATE OR REPLACE TRIGGER ABC
   AFTER INSERT OR UPDATE
   ON 2
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
BEGIN
   IF (INSERTING)
   THEN
      INSERT INTO 1
                  (SP_id,
                   TT_id,
                   LEGACY)
           VALUES (XXX_seq.NEXTVAL,
                   substr(:New.description, 1, instr(translate(:New.description, '1234567890', '**********'),'*')-2) ,
                   SUBSTR (:NEW.uid, 1, 30));
   END IF;

END ABC;
/


I need to take the parsed character field [substr(:New.description, 1, instr(translate(:New.description, '1234567890', '**********'),'*')-2) ,]
then verify its value with table 3's NAME field. Once the correct name is found I need to grab the table3.T_id and place it in the TT_id in table 1. Should this all be done in a trigger or should I try to create a procedure that is then called out? Any help, advice, or examples would be appreciated.
Previous Topic: sql code asking for username and password when running a batch file
Next Topic: forgot my system password
Goto Forum:
  


Current Time: Thu Dec 08 12:41:42 CST 2016

Total time taken to generate the page: 0.05434 seconds