|
aspardeshi
Starting Member
3 Posts |
 Posted - 2010-09-23 : 06:17:36
|
| 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 updated 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 |
|
insert trigger is not updating the inserted row