not updating inserted row in an insert trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » not updating inserted row in an insert...

not updating inserted row in an insert trigger

Rate Topic

Display Mode

Topic Options

Author

Message

aspardeshi

Posted Thursday, September 23, 2010 4:24 AM

SSC 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

Stewart "Arturius" Campbell

Posted Thursday, September 23, 2010 7:40 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:49 AM
Points: 3,123, Visits: 4,310

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

aspardeshi

Posted Thursday, September 23, 2010 8:01 AM

SSC 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

aspardeshi

Posted Thursday, September 23, 2010 8:04 AM

SSC 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

darkfour

Posted Thursday, September 23, 2010 9:48 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, February 11, 2013 1:27 PM
Points: 75, Visits: 944

check the cursor definition

"SELECT inserted.co, inserted.collegecd, inserted.taxcd, inserted.
inserted.[basic],"

Post #992145

Jack Corbett

Posted Thursday, September 23, 2010 10:27 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:22 PM
Points: 10,571, Visits: 11,871

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

aspardeshi

Posted Friday, September 24, 2010 12:50 AM

SSC 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

aspardeshi

Posted Friday, September 24, 2010 1:18 AM

SSC 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. Smile

Thank you so much.

I was very good at sql but variety spoiled me !

Post #992523

« Prev Topic | Next Topic »

Permissions