Hi - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Author

Topic

frank ifeka
Starting Member

4 Posts

Posted - 2009-07-27 : 10:57:38

Thanks for ur reply. I had already done some correction 2 d script,this is the error i got

Server: Msg 170, Level 15, State 1, Procedure PENCOMREF_GEN, Line 72
Line 72: Incorrect syntax near 'docRef'.

This is the script i ran

CREATE TRIGGER PENCOMREF_GEN ON DOCSADM.PROFILE
/* Keeps the series table up todate with changes made to profile table */
FOR INSERT
AS
/* Begin variables declaration */
BEGIN

declare @yearDate datetime,@pencomNo int,@userID nvarchar(8),@groupID nvarchar(20),@typeID nvarchar(10),@tempVar1 nvarchar(20),@tempVar2 nvarchar(20),@tempVar3 nvarchar(20),@lastNumber nvarchar(20),@docRef nvarchar(80),@lastrow bigint

/* First delete from series table*/

/* Asign the the pencom number a last identifier, with first customer being zero */

set @lastNumber = 0

/* Check the number of document in the DM System and asign it value to lastNumber variable */

select @lastrow = max(DOCNUMBER)
from docsadm.profile

/* Asign the values of these DM tables to the local varibale */

select @yeardate = creation_date,
@pencomno = docnumber,
@tempvar1 = typist,
@tempvar2 = documenttype
from docsadm.profile where DOCNUMBER = @lastrow

select @tempVar3 = primary_group,
@userid = user_id
from docsadm.people where system_id= @tempvar1

select @typeID = type_id
from docsadm.DOCUMENTTYPES where SYSTEM_ID=tempvar2

select @groupID = GROUP_ID from docsadm.GROUPS
where SYSTEM_ID=@tempVar3

update dbo.SERIES set @LASTNUMBER=LASTNUMBER + 1
where GROUP_ID=@tempVar3 and TYPE_ID=@tempvar2 and DOCYEAR=@yeardate

select @lastNumber = LASTNUMBER from dbo.SERIES
where GROUP_ID=@tempVar3 and TYPE_ID=@tempvar2 and DOCYEAR=@yeardate

/*

exception when others then

null;

end;*/

IF (@lastNumber<0)

insert into dbo.SERIES values (@tempVar3,@tempvar2,@yeardate,1)

set @lastNumber =1

END

docRef = @groupID ||'/'||@userID ||'/'||@typeID ||'/'||@to_char(@yeardate,'yyyy')||'/'||@lastNumber

update DOCSADM.profile set PENCOM_REFERENCE= @docRef

where docnumber = @pencomno

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 11:01:34

Hi

What is this. ||

docRef = @groupID ||'/'||@userID ||'/'||@typeID ||'/'||@to_char(@yeardate,'yyyy')||'/'||@lastNumber

-------------------------
R..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources