Re: Concatenating and copying columns data

From: Usman <khanusman_at_gmail.com>
Date: 11 Apr 2007 03:38:52 -0700
Message-ID: <1176287932.927626.96290@n76g2000hsh.googlegroups.com>

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:38:52 CDT