I have written the following trigger, I need to check if PROJ_ID
column does not exists in table then skip the logic. How can I
accomplish that? Oracle is throwing error at :new.PROJ_ID statement,
if I remove PROJ_ID column from the table. Is there any other way to
access PROJ_ID value from the new row?. Any help in this regard will
be appreciated.
CREATE OR REPLACE TRIGGER "USMANK_PW4"."PROCESS_AFTER" BEFORE
INSERT ON "USMANK_PW4"."PROCESS" FOR EACH ROW
DECLARE
PROC_KEYTAG VARCHAR (10);
PROC_PROJID VARCHAR (100);
PROC_ID VARCHAR (100);
INSTID numeric(20);
fldCount NUMBER(5);
startpos int; len int; asciistr varchar2(8000); onechar CHAR(1);
begin
asciistr := '';
startpos := 0;
SELECT COUNT('PROJ_ID') INTO fldCount FROM ALL_TAB_COLUMNS WHERE
TABLE_NAME ='PROCESS' AND COLUMN_NAME = 'PROJ_ID';
IF (fldCount != 1) THEN
len := length(:new.PROJ_ID);
if len > 0 then
while startpos < len loop
startpos := startpos + 1;
onechar := substr(:new.PROJ_ID,startpos,1);
if onechar >= '0' and onechar <= '9' then
asciistr := asciistr || onechar;
else
asciistr := asciistr || ASCII(onechar);
end if;
end loop;
end if;
end if;
asciistr := asciistr || :new.KEYTAG;
INSTID := TO_NUMBER(asciistr);
:new.ODS_INST_ID := INSTID;
end;
On Apr 10, 5:01 am, "joel garry" <joel-ga..._at_home.com> wrote:
> On Apr 9, 6:33 am, "Usman" <khanus..._at_gmail.com> wrote:
>
> > Yes, this is the tiny part of the bigger business task. I have
> > mentioned the problem in best possible words. I don't have time to go
> > through literature and will be glad if someone suggests something to
> > get me going.
>
> So, why is it are you asking this identical question in both cdos and
> mpsp?
>
> And why is it you don't have time to "go through literature?"
>
> jg
> --
> @home.com is bogus.
> But then again, aren't so many things?
Received on Wed Apr 11 2007 - 05:21:48 CDT
