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 : 09:05:44

Good day guys, pls I have a problem with this trigger I created.

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, @pencomno, @tempvar1, @tempvar2,@tempvar3,@userID, @groupID, @typeID = creation_date, docnumber, typist, documenttype, primary_group,user_id, group_id, type_id
from docsadm.profile, docsadm.people,docsadm.GROUPS,docsadm.DOCUMENTTYPES where DOCNUMBER = @lastrow and system_id= @tempvar1 and SYSTEM_ID = @tempvar3 and SYSTEM_ID = tempvar2

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 docsadm.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

I want to know why it gives me this error message
Server: Msg 141, Level 15, State 1, Procedure PENCOMREF_GEN, Line 27
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Server: Msg 170, Level 15, State 1, Procedure PENCOMREF_GEN, Line 58
Line 58: Incorrect syntax near 'docRef'.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 09:11:33

hi try this...

 
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 ,
               @tempvar3= primary_group,
               @userID  = user_id      ,
               @groupID = group_id     ,
               @typeID  = type_id
        FROM   docsadm.profile,
               docsadm.people ,
               docsadm.GROUPS ,
               docsadm.DOCUMENTTYPES
        WHERE  DOCNUMBER = @lastrow
           AND system_id = @tempvar1
           AND SYSTEM_ID = @tempvar3
           AND SYSTEM_ID = tempvar2
        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   docsadm.SERIES VALUES
               (
                      @tempVar3,
                      @tempvar2,
                      @yeardate,
                      1
               )
        SET @lastNumber =1
END

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

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 09:15:54

Hi

But am wondering your WHERE Clause. I don’t think so your query will work.

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

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 09:18:55

Hi

Am wondering this colored part

SELECT @yeardate= creation_date,
               @pencomno= docnumber    ,
          @tempvar1= typist       ,
               @tempvar2= documenttype ,
               @tempvar3= primary_group,  
               @userID  = user_id      ,
               @groupID = group_id     ,
               @typeID  = type_id
        FROM   docsadm.profile,
               docsadm.people ,
               docsadm.GROUPS ,
               docsadm.DOCUMENTTYPES
        WHERE  DOCNUMBER = @lastrow
      AND system_id = @tempvar1
           AND SYSTEM_ID = @tempvar3  
           AND SYSTEM_ID = tempvar2

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

frank ifeka
Starting Member

4 Posts

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

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:02:58

Hi

What is this. ||

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

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

frank ifeka
Starting Member

4 Posts

Posted - 2009-07-27 : 13:21:02

This line generates the document reference.
The document reference can be like 992/SN000111/0110/2007/5, where “992” denotes the Group ID, “SN000111” the User ID, “0110” the Document Type ID, “2007” the year of the creation date, and “5” the running sequence for the combination (Group ID, Document Type ID, Year).

Really this was originally written on an oracle database, i jus tried 2 interprete it on a SQL server Database. I just need to run it on a SQL Database and as u knw the sntax r different

this is how it was originally written.

create or replace trigger DOCREF_GEN
after insert on profile

DECLARE
yearDate date;
docNo NUMBER;
userID varchar2(8);
groupID varchar2(20);
typeID varchar2(10);
tempVar1 varchar2(20);
tempVar2 varchar2(20);
tempVar3 varchar2(20);
lastNumber varchar2(20);
docRef varchar2(80);
lastrow NUMBER;

begin
lastNumber:=0;
select max(DOCNUMBER) into lastrow from docsadm.profile;

select creation_date,docnumber,typist,documenttype into yeardate,docno,tempvar1,tempvar2
from docsadm.profile where DOCNUMBER=lastrow;

begin
select primary_group,user_id into tempVar3,userid
from docsadm.people where system_id=tempvar1;

select type_id into typeID
from docsadm.DOCUMENTTYPES where SYSTEM_ID=tempvar2;

select GROUP_ID into groupID from docsadm.GROUPS
where SYSTEM_ID=tempVar3;

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

select LASTNUMBER into lastNumber from docsadm.SERIES
where GROUP_ID=tempVar3 and TYPE_ID=tempvar2 and DOCYEAR=yeardate;
exception when others then
null;
end;

if lastNumber=0 then
begin
insert into docsadm.SERIES values (tempVar3,tempvar2,yeardate,1);
lastNumber:=1;
end;
end if;

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

update DOCSADM.profile set DOC_REFERENCE= docRef
where docnumber=docno;

end;
/

This is how I wrote it in SQL server

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources