damn, please ignore my last post.
On Apr 11, 3:38 pm, "Usman" <khanus..._at_gmail.com> wrote:
> I apologize for sending the below query on this forum. I am having
> similar problem with SQL server version of this trigger (attached
> below). Can anyone please recommend how to workaround this issue?
>
> Usman
>
> CREATE TRIGGER [usmank2].[process_ods_inst_id_trig] ON
> usmank2.PROCESS
> FOR INSERT AS
>
> begin
>
> DECLARE @PROC_KEYTAG VARCHAR (10)
> DECLARE @PROC_PROJID VARCHAR (100)
> DECLARE @PROC_ID VARCHAR (100)
> DECLARE @INSTID numeric(20)
>
> declare @start int , @len int , @asciistr varchar(8000),@onechar
> CHAR(1)
> declare @ctr int
>
> SELECT @PROC_KEYTAG = KEYTAG FROM INSERTED
> SELECT @PROC_PROJID = PROJ_ID FROM INSERTED
>
> set @asciistr = ''
> set @start = 0
>
> select @ctr= count(*) from syscolumns where name='PROJ_ID' and
> id=object_id('PROCESS')
>
> if @ctr > 0
> begin
> set @len = len(@PROC_PROJID)
> if @len > 0
> while @start < @len
> begin
> set @start = @start + 1
> set @onechar = substring(@PROC_PROJID,@start,
> 1)
> if isnumeric(@onechar) = 1
> set @asciistr = @asciistr + @onechar
> else
> set @asciistr = @asciistr +
> ascii(@onechar)
>
> end
> end
> set @asciistr = @asciistr + @PROC_KEYTAG
>
> set @INSTID = convert (numeric(20), @asciistr)
>
> UPDATE [PROCESS] SET ODS_INST_ID = @ctr WHERE KEYTAG = @PROC_KEYTAG
>
> END
>
> On Apr 11, 3:21 pm, "Usman" <khanus..._at_gmail.com> wrote:
>
> > 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:40:31 CDT
