Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

not updating inserted row in an insert trigger Expand / Collapse
Author
Message
Posted Thursday, September 23, 2010 4:24 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 28, 2010 2:50 AM
Points: 28, Visits: 43
Hi !

I have a insert trigger which is used on canteen invoice detail table. It should ideally calculate tax on every invoice item of the invoice. the percentages are given in taxmaster and you can define upto 5 combinations of percentage calculations of tax in one tax code so 5 columns for the tax values. so you can have 5 types of taxes at the most per line of invoice.

the trigger goes as below. my problem is that i want to update the 5 inserted columns acc1,acc2,acc3,acc4,acc5 when the tax values are calculated after taken from tax master table. the trigger is not giving any compilation errors but is just keying in zero in all the fields. please let me know where am i going wrong.

secondly also please let me know is it possible to update the inserted row in sql server 2005 in insert trigger. if not then please let me know other ways. I am programmer in oracle for past 15 years but this is just heavy for me.

ALTER trigger [dbo].[canteeninvoicedetlins] on [dbo].[college_canteeninvoicedetail]
after insert AS
DECLARE @SWV_NEW_CO VARCHAR(255)
DECLARE @SWV_NEW_COLLEGECD VARCHAR(255)
DECLARE @SWV_NEW_TAXCD VARCHAR(255)
DECLARE @SWV_NEW_BASIC VARCHAR(255)
DECLARE @SWV_NEW_YEARCD VARCHAR(255)
DECLARE @SWV_NEW_CODE VARCHAR(255)
DECLARE @SWV_NEW_DOCNO VARCHAR(255)
DECLARE @SWV_NEW_DOCDATE VARCHAR(255)
DECLARE @SWV_NEW_CUSTID VARCHAR(255)
DECLARE @SWV_NEW_ACCID VARCHAR(255)
DECLARE @SWV_NEW_ITEMCD VARCHAR(255)
DECLARE @SWV_NEW_QTY VARCHAR(255)
DECLARE @SWV_Cursor_For_NEW CURSOR
SET @SWV_Cursor_For_NEW = CURSOR FOR SELECT co, collegecd, taxcd, basic, yearcd, code, docno, docdate, custid, accid, itemcd, qty FROM inserted
OPEN @SWV_Cursor_For_NEW
FETCH NEXT FROM @SWV_Cursor_For_NEW INTO @SWV_NEW_CO,@SWV_NEW_COLLEGECD,@SWV_NEW_TAXCD,@SWV_NEW_BASIC,@SWV_NEW_YEARCD,
@SWV_NEW_CODE,@SWV_NEW_DOCNO,@SWV_NEW_DOCDATE,@SWV_NEW_CUSTID,@SWV_NEW_ACCID,
@SWV_NEW_ITEMCD,@SWV_NEW_QTY
WHILE @@FETCH_STATUS = 0
begin
DECLARE @vco INT
DECLARE @vcollegecd INT
DECLARE @vtaxcd INT
DECLARE @vseq INT
DECLARE @vaccid INT
DECLARE @vperc INT
DECLARE @vonseq1 INT
DECLARE @vonseq2 INT
DECLARE @vonseq3 INT
DECLARE @vonseq4 INT
DECLARE @vonseq5 INT
DECLARE @vfirstvalue INT
DECLARE @vsecondvalue INT
DECLARE @vthirdvalue INT
DECLARE @vfourthvalue INT
DECLARE @vfifthvalue INT
DECLARE @vtempone INT
DECLARE @vtemptwo INT
DECLARE @vtempthree INT
DECLARE @vtempfour INT
DECLARE @vtempfive INT
DECLARE @c1 CURSOR
SET @c1 = CURSOR FOR select co,collegecd,taxcd,seq,accid,perc,onseq1,
onseq2,onseq3,onseq4,onseq5 from college_taxdetail
where co = @SWV_NEW_CO and collegecd = @SWV_NEW_COLLEGECD
and taxcd = @SWV_NEW_TAXCD order by seq
open @c1
while 1 = 1
begin
fetch @c1 into @vco,@vcollegecd,@vtaxcd,@vseq,@vaccid,@vperc,@vonseq1,@vonseq2,@vonseq3,
@vonseq4,@vonseq5
if @@FETCH_STATUS <> 0
BREAK

if @vseq = 1
if @vonseq1 = 0
SET @vfirstvalue = ISNULL(@SWV_NEW_BASIC,0)*(ISNULL(@vperc,0)/100)
else
SET @vfirstvalue = 0


SET @vtempone = 0
SET @vtemptwo = 0
SET @vtempthree = 0
SET @vtempfour = 0
SET @vtempfive = 0
if @vseq = 2
begin
if @vonseq1 = 0
SET @vtempone = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq1 = 1
SET @vtempone = ISNULL(@vfirstvalue,0)

if @vonseq2 = 0
SET @vtemptwo = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq2 = 1
SET @vtemptwo = ISNULL(@vfirstvalue,0)

SET @vsecondvalue =(ISNULL(@vtempone,0)+ISNULL(@vtemptwo,0))*(ISNULL(@vperc,0)/100)
end

SET @vtempone = 0
SET @vtemptwo = 0
SET @vtempthree = 0
SET @vtempfour = 0
SET @vtempfive = 0
if @vseq = 3
begin
if @vonseq1 = 0
SET @vtempone = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq1 = 1
SET @vtempone = ISNULL(@vfirstvalue,0)

if @vonseq1 = 2
SET @vtempone = ISNULL(@vsecondvalue,0)

if @vonseq2 = 0
SET @vtemptwo = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq2 = 1
SET @vtemptwo = ISNULL(@vfirstvalue,0)

if @vonseq2 = 2
SET @vtemptwo = ISNULL(@vsecondvalue,0)

if @vonseq3 = 0
SET @vtempthree = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq3 = 1
SET @vtempthree = ISNULL(@vfirstvalue,0)

if @vonseq3 = 2
SET @vtempthree = ISNULL(@vsecondvalue,0)

SET @vthirdvalue =(ISNULL(@vtempone,0)+ISNULL(@vtemptwo,0)+ISNULL(@vtempthree,0))*(ISNULL(@vperc,0)/100)
end

SET @vtempone = 0
SET @vtemptwo = 0
SET @vtempthree = 0
SET @vtempfour = 0
SET @vtempfive = 0
if @vseq = 4
begin
if @vonseq1 = 0
SET @vtempone = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq1 = 1
SET @vtempone = ISNULL(@vfirstvalue,0)

if @vonseq1 = 2
SET @vtempone = ISNULL(@vsecondvalue,0)

if @vonseq1 = 3
SET @vtempone = ISNULL(@vthirdvalue,0)

if @vonseq2 = 0
SET @vtemptwo = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq2 = 1
SET @vtemptwo = ISNULL(@vfirstvalue,0)

if @vonseq2 = 2
SET @vtemptwo = ISNULL(@vsecondvalue,0)

if @vonseq2 = 3
SET @vtemptwo = ISNULL(@vthirdvalue,0)

if @vonseq3 = 0
SET @vtempthree = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq3 = 1
SET @vtempthree = ISNULL(@vfirstvalue,0)

if @vonseq3 = 2
SET @vtempthree = ISNULL(@vsecondvalue,0)

if @vonseq3 = 3
SET @vtempthree = ISNULL(@vthirdvalue,0)

if @vonseq4 = 0
SET @vtempfour = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq4 = 1
SET @vtempfour = ISNULL(@vfirstvalue,0)

if @vonseq4 = 2
SET @vtempfour = ISNULL(@vsecondvalue,0)

if @vonseq4 = 3
SET @vtempfour = ISNULL(@vthirdvalue,0)

SET @vfourthvalue =(ISNULL(@vtempone,0)+ISNULL(@vtemptwo,0)+ISNULL(@vtempthree,0)+ISNULL(@vtempfour,0))*(ISNULL(@vperc,0)/100)
end

SET @vtempone = 0
SET @vtemptwo = 0
SET @vtempthree = 0
SET @vtempfour = 0
SET @vtempfive = 0
if @vseq = 5
begin
if @vonseq1 = 0
SET @vtempone = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq1 = 1
SET @vtempone = ISNULL(@vfirstvalue,0)

if @vonseq1 = 2
SET @vtempone = ISNULL(@vsecondvalue,0)

if @vonseq1 = 3
SET @vtempone = ISNULL(@vthirdvalue,0)

if @vonseq1 = 4
SET @vtempone = ISNULL(@vfourthvalue,0)

if @vonseq2 = 0
SET @vtemptwo = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq2 = 1
SET @vtemptwo = ISNULL(@vfirstvalue,0)

if @vonseq2 = 2
SET @vtemptwo = ISNULL(@vsecondvalue,0)

if @vonseq2 = 3
SET @vtemptwo = ISNULL(@vthirdvalue,0)

if @vonseq2 = 4
SET @vtemptwo = ISNULL(@vfourthvalue,0)

if @vonseq3 = 0
SET @vtempthree = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq3 = 1
SET @vtempthree = ISNULL(@vfirstvalue,0)

if @vonseq3 = 2
SET @vtempthree = ISNULL(@vsecondvalue,0)

if @vonseq3 = 3
SET @vtempthree = ISNULL(@vthirdvalue,0)

if @vonseq3 = 4
SET @vtempthree = ISNULL(@vfourthvalue,0)

if @vonseq4 = 0
SET @vtempfour = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq4 = 1
SET @vtempfour = ISNULL(@vfirstvalue,0)

if @vonseq4 = 2
SET @vtempfour = ISNULL(@vsecondvalue,0)

if @vonseq4 = 3
SET @vtempfour = ISNULL(@vthirdvalue,0)

if @vonseq4 = 4
SET @vtempfour = ISNULL(@vfourthvalue,0)

if @vonseq5 = 0
SET @vtempfive = ISNULL(@SWV_NEW_BASIC,0)

if @vonseq5 = 1
SET @vtempfive = ISNULL(@vfirstvalue,0)

if @vonseq5 = 2
SET @vtempfive = ISNULL(@vsecondvalue,0)

if @vonseq5 = 3
SET @vtempfive = ISNULL(@vthirdvalue,0)

if @vonseq5 = 4
SET @vtempfive = ISNULL(@vfourthvalue,0)

SET @vfifthvalue =(ISNULL(@vtempone,0)+ISNULL(@vtemptwo,0)+ISNULL(@vtempthree,0)+ISNULL(@vtempfour,0)+ISNULL(@vtempfive,0))*(ISNULL(@vperc,0)/100)
end

update [dbo].[college_canteeninvoicedetail] set acc1 = @vfirstvalue where co = @SWV_NEW_CO and
collegecd = @SWV_NEW_COLLEGECD and yearcd = @SWV_NEW_YEARCD and code = @SWV_NEW_CODE and
docno = @SWV_NEW_DOCNO and docdate = @SWV_NEW_DOCDATE and
custid = @SWV_NEW_CUSTID and accid = @SWV_NEW_ACCID and itemcd = @SWV_NEW_ITEMCD and
qty = @SWV_NEW_QTY and basic = @SWV_NEW_BASIC and taxcd = @SWV_NEW_TAXCD
update [dbo].[college_canteeninvoicedetail] set acc2 = @vsecondvalue where co = @SWV_NEW_CO and
collegecd = @SWV_NEW_COLLEGECD and yearcd = @SWV_NEW_YEARCD and code = @SWV_NEW_CODE and
docno = @SWV_NEW_DOCNO and docdate = @SWV_NEW_DOCDATE and
custid = @SWV_NEW_CUSTID and accid = @SWV_NEW_ACCID and itemcd = @SWV_NEW_ITEMCD and
qty = @SWV_NEW_QTY and basic = @SWV_NEW_BASIC and taxcd = @SWV_NEW_TAXCD
update [dbo].[college_canteeninvoicedetail] set acc3 = @vthirdvalue where co = @SWV_NEW_CO and
collegecd = @SWV_NEW_COLLEGECD and yearcd = @SWV_NEW_YEARCD and code = @SWV_NEW_CODE and
docno = @SWV_NEW_DOCNO and docdate = @SWV_NEW_DOCDATE and
custid = @SWV_NEW_CUSTID and accid = @SWV_NEW_ACCID and itemcd = @SWV_NEW_ITEMCD and
qty = @SWV_NEW_QTY and basic = @SWV_NEW_BASIC and taxcd = @SWV_NEW_TAXCD
update [dbo].[college_canteeninvoicedetail] set acc4 = @vfourthvalue where co = @SWV_NEW_CO and
collegecd = @SWV_NEW_COLLEGECD and yearcd = @SWV_NEW_YEARCD and code = @SWV_NEW_CODE and
docno = @SWV_NEW_DOCNO and docdate = @SWV_NEW_DOCDATE and
custid = @SWV_NEW_CUSTID and accid = @SWV_NEW_ACCID and itemcd = @SWV_NEW_ITEMCD and
qty = @SWV_NEW_QTY and basic = @SWV_NEW_BASIC and taxcd = @SWV_NEW_TAXCD
update [dbo].[college_canteeninvoicedetail] set acc5 = @vfifthvalue where co = @SWV_NEW_CO and
collegecd = @SWV_NEW_COLLEGECD and yearcd = @SWV_NEW_YEARCD and code = @SWV_NEW_CODE and
docno = @SWV_NEW_DOCNO and docdate = @SWV_NEW_DOCDATE and
custid = @SWV_NEW_CUSTID and accid = @SWV_NEW_ACCID and itemcd = @SWV_NEW_ITEMCD and
qty = @SWV_NEW_QTY and basic = @SWV_NEW_BASIC and taxcd = @SWV_NEW_TAXCD
end
close @c1
FETCH NEXT FROM @SWV_Cursor_For_NEW INTO @SWV_NEW_CO,@SWV_NEW_COLLEGECD,@SWV_NEW_TAXCD,@SWV_NEW_BASIC,@SWV_NEW_YEARCD,
@SWV_NEW_CODE,@SWV_NEW_DOCNO,@SWV_NEW_DOCDATE,@SWV_NEW_CUSTID,@SWV_NEW_ACCID,
@SWV_NEW_ITEMCD,@SWV_NEW_QTY
end
CLOSE @SWV_Cursor_For_NEW


I was very good at sql but variety spoiled me !
Post #991873
Posted Thursday, September 23, 2010 7:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 3,850, Visits: 4,988
will this help at all?

ALTER trigger [dbo].[canteeninvoicedetlins] on [dbo].[college_canteeninvoicedetail]
after insert AS
DECLARE @SWV_NEW_CO VARCHAR(255),
@SWV_NEW_COLLEGECD VARCHAR(255),
@SWV_NEW_TAXCD VARCHAR(255),
@SWV_NEW_BASIC VARCHAR(255),
@SWV_NEW_YEARCD VARCHAR(255),
@SWV_NEW_CODE VARCHAR(255),
@SWV_NEW_DOCNO VARCHAR(255),
@SWV_NEW_DOCDATE VARCHAR(255),
@SWV_NEW_CUSTID VARCHAR(255),
@SWV_NEW_ACCID VARCHAR(255),
@SWV_NEW_ITEMCD VARCHAR(255),
@SWV_NEW_QTY VARCHAR(255),
@vseq INT,
@vaccid INT,
@vperc INT,
@vonseq1 INT,
@vonseq2 INT,
@vonseq3 INT,
@vonseq4 INT,
@vonseq5 INT,

DECLARE SWV_Cursor_For_NEW CURSOR
FOR SELECT inserted.co, inserted.collegecd, inserted.taxcd, inserted.
inserted.[basic], inserted.yearcd, inserted.code, inserted.docno,
inserted.docdate, inserted.custid, inserted.accid, inserted.itemcd,
inserted.qty, college_taxdetail.seq, college_taxdetail.accid,
college_taxdetail.perc,onseq1, college_taxdetail.onseq2, college_taxdetail.onseq3,
college_taxdetail.onseq4, college_taxdetail.onseq5
FROM inserted
join college_taxdetail on college_taxdetail.co = inserted.co
and college_taxdetail.collegecd = inserted.collegecd
and college_taxdetail.taxcd = inserted.taxcd
order by inserted.co, inserted.collegecd, inserted.taxcd, college_taxdetail.seq

OPEN SWV_Cursor_For_NEW
FETCH NEXT FROM SWV_Cursor_For_NEW
INTO @SWV_NEW_CO,@SWV_NEW_COLLEGECD,@SWV_NEW_TAXCD,@SWV_NEW_BASIC,@SWV_NEW_YEARCD,
@SWV_NEW_CODE,@SWV_NEW_DOCNO,@SWV_NEW_DOCDATE,@SWV_NEW_CUSTID,@SWV_NEW_ACCID,
@SWV_NEW_ITEMCD,@SWV_NEW_QTY, @vseq, @vaccid, @vperc, @vonseq1, @vonseq2,
@vonseq3, @vonseq4, @vonseq5
WHILE @@FETCH_STATUS = 0
begin
DECLARE @vfirstvalue INT,
@vsecondvalue INT,
@vthirdvalue INT,
@vfourthvalue INT,
@vfifthvalue INT

if @vseq = 1
if @vonseq1 = 0
SELECT @vfirstvalue = ISNULL(@SWV_NEW_BASIC,0)*(ISNULL(@vperc,0)/100)
else
SELECT @vfirstvalue = 0

if @vseq = 2 begin
SELECT @vsecondvalue =(ISNULL(CASE
WHEN @vonseq1 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq1 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)+
ISNULL(CASE
WHEN @vonseq2 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq2 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)
)*(ISNULL(@vperc,0)/100)
end

if @vseq = 3 begin
SELECT @vthirdvalue =(ISNULL(CASE
WHEN @vonseq1 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq1 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq1 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)+
ISNULL(CASE
WHEN @vonseq2 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq2 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq2 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)+
ISNULL(CASE
WHEN @vonseq3 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq3 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq3 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)
)*(ISNULL(@vperc,0)/100)
end

if @vseq = 4 begin
SELECT @vfourthvalue =(ISNULL(CASE
WHEN @vonseq1 = 3 THEN ISNULL(@vthirdvalue,0)
WHEN @vonseq1 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq1 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq1 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)+
ISNULL(CASE
WHEN @vonseq2 = 3 THEN ISNULL(@vthirdvalue,0)
WHEN @vonseq2 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq2 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq2 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)+
ISNULL(CASE
WHEN @vonseq4 = 3 THEN ISNULL(@vthirdvalue,0)
WHEN @vonseq3 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq3 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq3 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)+
ISNULL(CASE
WHEN @vonseq4 = 3 THEN ISNULL(@vthirdvalue,0)
WHEN @vonseq4 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq4 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq4 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)
)*(ISNULL(@vperc,0)/100)
end

if @vseq = 5 begin
SELECT @vfifthvalue =(ISNULL(CASE
WHEN @vonseq1 = 4 THEN ISNULL(@vfourthvalue,0)
WHEN @vonseq1 = 3 THEN ISNULL(@vthirdvalue,0)
WHEN @vonseq1 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq1 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq1 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)+
ISNULL(CASE
WHEN @vonseq2 = 4 THEN ISNULL(@vfourthvalue,0)
WHEN @vonseq2 = 3 THEN ISNULL(@vthirdvalue,0)
WHEN @vonseq2 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq2 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq2 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)+
ISNULL(CASE
WHEN @vonseq5 = 4 THEN ISNULL(@vfourthvalue,0)
WHEN @vonseq4 = 3 THEN ISNULL(@vthirdvalue,0)
WHEN @vonseq3 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq3 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq3 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)+
ISNULL(CASE
WHEN @vonseq4 = 4 THEN ISNULL(@vfourthvalue,0)
WHEN @vonseq4 = 3 THEN ISNULL(@vthirdvalue,0)
WHEN @vonseq4 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq4 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq4 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)+
ISNULL(CASE
WHEN @vonseq5 = 4 THEN ISNULL(@vfourthvalue,0)
WHEN @vonseq5 = 3 THEN ISNULL(@vthirdvalue,0)
WHEN @vonseq5 = 2 THEN ISNULL(@vsecondvalue,0)
WHEN @vonseq5 = 1 THEN ISNULL(@vfirstvalue,0)
WHEN @vonseq5 = 0 THEN ISNULL(@SWV_NEW_BASIC,0)
END,0)
)*(ISNULL(@vperc,0)/100)
end

update [dbo].[college_canteeninvoicedetail]
set acc1 = @vfirstvalue,
acc2 = @vsecondvalue,
acc3 = @vthirdvalue,
acc4 = @vfourthvalue,
acc5 = @vfifthvalue
where co = @SWV_NEW_CO
and collegecd = @SWV_NEW_COLLEGECD
and yearcd = @SWV_NEW_YEARCD
and code = @SWV_NEW_CODE
and docno = @SWV_NEW_DOCNO
and docdate = @SWV_NEW_DOCDATE
and custid = @SWV_NEW_CUSTID
and accid = @SWV_NEW_ACCID
and itemcd = @SWV_NEW_ITEMCD
and qty = @SWV_NEW_QTY
and basic = @SWV_NEW_BASIC
and taxcd = @SWV_NEW_TAXCD

FETCH NEXT FROM SWV_Cursor_For_NEW
INTO @SWV_NEW_CO,@SWV_NEW_COLLEGECD,@SWV_NEW_TAXCD,@SWV_NEW_BASIC,@SWV_NEW_YEARCD,
@SWV_NEW_CODE,@SWV_NEW_DOCNO,@SWV_NEW_DOCDATE,@SWV_NEW_CUSTID,@SWV_NEW_ACCID,
@SWV_NEW_ITEMCD,@SWV_NEW_QTY, @vseq, @vaccid, @vperc, @vonseq1, @vonseq2,
@vonseq3, @vonseq4, @vonseq5
end
CLOSE SWV_Cursor_For_NEW
deallocate SWV_Cursor_For_NEW



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #992014
Posted Thursday, September 23, 2010 8:01 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 28, 2010 2:50 AM
Points: 28, Visits: 43
Hi Stewart ,

Thank you a lot. That was really great efforts from your end, I have copied and tried to compile but it is giving error at line 30 which comes to declare variable @vperc int.

I had tried to rectify it but it is not going. please look into it so that I can post the results here.

Thank you once again for your extended efforts in this respect.


I was very good at sql but variety spoiled me !
Post #992036
Posted Thursday, September 23, 2010 8:04 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 28, 2010 2:50 AM
Points: 28, Visits: 43
Hi Stewart,

when I shuffled @vperc int, to up 3 locations it is still giving error at line 30 for another variable and the error code I get is as follows:-

Msg 156, Level 15, State 1, Procedure canteeninvoicedetlins, Line 30
Incorrect syntax near the keyword 'DECLARE'.

Hope this helps.


I was very good at sql but variety spoiled me !
Post #992042
Posted Thursday, September 23, 2010 9:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 27, 2014 11:01 AM
Points: 75, Visits: 946
check the cursor definition

"SELECT inserted.co, inserted.collegecd, inserted.taxcd, inserted.
inserted.[basic],"
Post #992145
Posted Thursday, September 23, 2010 10:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 11,141, Visits: 12,883
All your variables are defined as INT which means you are getting all INT values . This:

(ISNULL(@vperc,0)/100)

will return a 0 for any @vperc values that are below 100 because the conversion to INT is doing truncation. And anything multiplied by 0 equals 0. One way to fix it is to make @vperc a float/decimal/numeric type with a precision > 0 or you can change all the places you have "/100" to "/100.00"
For verification run this:

DECLARE @vperc INT, @vperc2 DECIMAL(10, 2)

SET @vperc = 99
SET @vperc2 = 99

SELECT
@vperc/100 AS int_values,
@vperc/100.0 AS one_hundred_with_decimal,
@vperc2/100 AS variable_as_decimal

I'd also suggest you look into a way to do this without a cursor. As the cursor is killing your performance and causing your transaction to be longer than necessary. I don't have time right now to propose a set-based solution, but if I have some time later I'll give it a shot.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #992173
Posted Friday, September 24, 2010 12:50 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 28, 2010 2:50 AM
Points: 28, Visits: 43
Hello Corbett, Darkfour, and Stewart,

Thank you for your big response. I am overwhelmed. But there is one thing which I have noticed in the new script given by Stewart is the join condition. Stewart is joining detail and detail detail table. So there will be 5 records created in cursor and I need 5 records to be processed in loop of taxdetail for every 1 record of canteeninvoicedetl. So I think there is a mistake there. In the cursor taxdetail is the detail detail table and canteeninvoicedetl is the detail table so for every canteeninvoicedetl single record there are 5 records of taxdetail.

I will explain in a bit depth here

canteeninvoicedetl table

co collegecd yearcd basic taxcd
1 1 1 5000 78


taxdetail table
co collegecd taxcd accid seqno perc onseq1 onseq2 onseq3 onseq4 onseq5
1 1 78 100 1 12.5 0 null null null null
1 1 78 105 2 4 0 1 null null null
1 1 78 107 3 2 2 null null null null
1 1 78 112 4 1 2 3 null null null
1 1 78 109 5 1.5 1 4 null null null

when the first record in canteeninvoicedetl come then then taxdetail 5 records will be retrieved from taxdetail table ordered by sequence in first sequence (seqno column)

1. the first sequence onseq1 is 0 means on basic
2. the second sequence onseq1 is 0 and onseq2 is 1 means 4 percent on basic+first value i.e 12.5%
3. the third sequence onseq1 is 2 means whatever value is calculated for sequence 2 it calculates 2 percent on that value.
4. the fourth sequence onseq1 is 2 and onseq2 is 3 means whatever values are calculated for sequence 2 and 3 it calculates 1% of that and stores in fourthvalue.
5. the fifth sequence onseq1 is 1 and onseq2 is 4 means whatever values are calculated for sequence 1 and 4 it calculates 1.5% of that and saves in fifthvalue.

This should be in a inside loop for of taxdetail for every record of canteeninvoicedetl.

so first canteeninvoicedetl loop and for every fetch the taxdetail loop.

I hope it is clear now. To my knowledge the stewart program I am not able to compile. Any help would be appreciated as per this logic. I have fitted this logic in oracle and is getting compiled successfully.

Please help.


I was very good at sql but variety spoiled me !
Post #992510
Posted Friday, September 24, 2010 1:18 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 28, 2010 2:50 AM
Points: 28, Visits: 43
Hello Professionals,

I would like to take this opportunity to thank every one of you. My project is resolved on my own trigger which I have posted. I just changed vperc declare statement to decimal 10,2.

I am overwhelmed by the expertise shown by all of you professionals. Hats off to you all.

Thank you so much.


I was very good at sql but variety spoiled me !
Post #992523
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse