September 23, 2010 at 4:24 am
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
September 23, 2010 at 7:40 am
This was removed by the editor as SPAM
September 23, 2010 at 8:01 am
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.
September 23, 2010 at 8:05 am
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.
September 23, 2010 at 9:48 am
check the cursor definition
"SELECT inserted.co, inserted.collegecd, inserted.taxcd, inserted.
inserted.[basic],"
September 23, 2010 at 10:27 am
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
Consultant - Straight Path Solutions
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
September 24, 2010 at 12:50 am
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.
September 24, 2010 at 1:18 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply