asked
Database enters null values alternately instead of variable value
CREATE Table Installments ( InslmntID int IDENTITY(1,1) CONSTRAINT Pk_Installment PRIMARY KEY CLUSTERED, InsallmentID AS ('Inst-' + CAST(InslmntID AS nvarchar)) PERSISTED CONSTRAINT unique_Installments UNIQUE NONCLUSTERED NOT NULL, InstallmentNo int NOT NULL, AdmsnID int CONSTRAINT Fk_Installment_Admission FOREIGN KEY REFERENCES Admission(AdmsnID), TotalAmount money NOT NULL, InstallmentAmount money NULL, BalanceAmount money NULL, InstallmentDate nvarchar(20) NULL, PaidDate nvarchar(20) NULL, PaymentOption nvarchar(20) NULL, CheqOrDDNo nvarchar(max) NULL, BankName nvarchar(50) NULL, BankBranch nvarchar(50) NULL, InstallmentDescription nvarchar(max) NULL, ChequeIsuuedDate nvarchar(20) NULL, PaymentDone nvarchar(10) NOT NULL CONSTRAINT default_Paid DEFAULT 'NotDone', UpdateBy nvarchar(30) NULL, UpdateDate nvarchar(30) NULL, UpdateTime nvarchar(30) NULL) Above is the structure of Installments table and its values are updated using stored procedure CREATE PROC UpdateInstallmentAmount @AdmsnID int, @NewInstallment money, @InstallmentNo int, @InstallmentDate nvarchar(20), @PaymentDone nvarchar(10) AS BEGIN TRY BEGIN TRANSACTION TR1 DECLARE @PreviousInstallmentDate nvarchar(20) DECLARE @PreviousPaidDate nvarchar(20) DECLARE @PresentInstallment money DECLARE @PresentBalance money DECLARE @Nextinstallment money DECLARE @MaxInstallment int SELECT @PresentInstallment = InstallmentAmount,@PresentBalance = BalanceAmount FROM Installments WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo SELECT @Nextinstallment = InstallmentAmount FROM Installments WHERE AdmsnID = @AdmsnID AND InstallmentNo = (@InstallmentNo + 1) UPDATE Installments SET InstallmentDate = @InstallmentDate WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo IF CAST(@PreviousInstallmentDate AS Datetime) > CAST(@InstallmentDate AS Datetime) BEGIN RAISERROR('Date entered Should not be before today',16,1) END ELSE BEGIN UPDATE Installments SET PaymentDone = @PaymentDone WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo END UPDATE Installments SET InstallmentAmount = @NewInstallment WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION TR1 END CATCH After update following trigger is fired to add a additional row if balance doesn't comes to 0 ALTER TRIGGER DeleteORAddInstallment ON Installments AFTER UPDATE AS DECLARE @AdmsnID int DECLARE @PresentInstallmentNo int DECLARE @NewInstallment money DEClARE @No_Of_Installments int DECLARE @PreviousBalance money DECLARE @InstallmentNo int DECLARE @PresentBalance money SELECT @AdmsnID = AdmsnID, @PresentInstallmentNo = InstallmentNO, @NewInstallment = InstallmentAmount, @PresentBalance = BalanceAmount FROM INSERTED SELECT @PreviousBalance = BalanceAmount FROM Installments WHERE AdmsnID = @AdmsnID AND InstallmentNo = (@PresentInstallmentNo - 1) DECLARE @LastInstallment int DECLARE @LastBalance money DECLARE @NewBalance money DECLARE @TotalAmount money DECLARE @PaidAmount money SELECT @LastInstallment = MAX(InstallmentNo) FROM Installments WHERE AdmsnId = @AdmsnID SELECT @LastBalance = BalanceAmount, @TotalAmount = TotalAmount FROM Installments WHERE AdmsnId = @AdmsnID AND InstallmentNo = @LastInstallment IF @LastBalance > 0 BEGIN SET @NewBalance = 0 SET @PaymentDone= 'NotDone' SET @LastInstallment = @LastInstallment + 1 EXEC InsertInstallmentsProc @LastInstallment, @AdmsnID, @TotalAmount, @LastBalance, @NewBalance, @PaymentDone UPDATE Payment SET Paid_Installments = @LastInstallment WHERE AdmsnID = @AdmsnID END For first time its value is correctly inserted EXEC UpdateInstallmentAmount 3,1500,6,'12/12/2010','Not Done'  But on second execution it places null in 'InstallmentAmount' column and this happens every alternate execution ![alt text][1] Please help me with this problem [1]:
http://img836.imageshack.us/img836/916/secondexecution.png
added images - the url wasn't the 'raw' image url, it was an html page containing the image :). Took a while to figure that one out! Thanks OSQA guys, for your help.
The trigger is broken and inserts a null value becuase you supply an `NULL` value to the stored procedure `InsertInstallmentsProc` when the trigger fires (The variable `@NewBalance` is initialised, left empty and passed to the sproc) This sproc cann also not be run and throws an exception because not all parameters are supplied in the trigger. ***Basically the design, as supplied, cannot work.*** I would suggest a complete re-design of the process and that you do so without the trigger. It would be quite simple to create a payment plan (installment entries) using the data from the table `dbo.Payment`. That said; I created an entry in the `dbo.Payment` table so that I had a reference to play with and came up with the following: INSERT INTO [dbo].[Payment] ([AdmsnID], [Package], [FeeAmount], [ManualDiscount], [DiscountInCash], [TotalFees], [No_Of_Installments], [Paid_Installments], [PaymentDate], [PaymentOption], [PaidAmount], [Balance], [ChequeNo], [BankName], [BankBranch], [ChequeIsuuedDate], [PaymentDescription], [UpdateBy], [UpdateDate], [UpdateTime]) SELECT 3, N'Course1', 30500.0000, N'N', 0.0000, 30500.0000, 10, 0, N'2010-09-01', N'Cash', 0.0000, 30500.0000, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL I then created the 10 installments using the following statement: INSERT dbo.Installments (InstallmentNo, AdmsnID, TotalAmount, InstallmentAmount, BalanceAmount, InstallmentDate, PaymentDone) SELECT T.N, P.AdmsnID, P.FeeAmount, 0, p.Balance, DATEADD(m, T.N - 1, p.PaymentDate), -- Monthly payment starting from PaymentDate 'Not Done' FROM dbo.Payment P INNER JOIN dbo.Tally T ON t.n <= P.No_Of_Installments -- create as many payments as necessary This statement uses a [Tally table][1] and the `No_of_Installments` column in `dbo.Payments` to generate ten empty installments for the `dbo.Installments` table. You could use this to do the initial fill of `dbo.Installments`, just change it to how you need it. The update would then be trivial and done as and when an installment is updated. I am not a great fan of keeping running totals as you are doing, there is always a risk of them getting out of sync. I trust `SUM()` to do its job and would generate this at runtime instead. I hope this helps you to get started, but without further input from you (especially a clearer explanation of what you want to do), I'm afraid I am out for this one. ---------- *P.S. For future reference if you require assistance (here or elsewhere), please supply the full DDL statements for all objects that are concerned, a couple of example entries for the tables, a sample output and a description of the process itself. It makes the task much easier for an outsider to get a grip of the issues that you are having and the reasons behind the decisions you have made.* [1]:
http://www.sqlservercentral.com/articles/T-SQL/62867/
Can u post the table definition for Admission Table
the scenerio is admsn id is foreign key in payment table and istallments when payment table values is inserted installments rows are also created with no of rows = no_of_installments value from payment table when in installments , balanceamount is still greater than 0 new row is inserted with installmentNo + 1 added to last value CREATE PROC InsertInstallmentsProc ( @InstallmentNo int , @AdmsnID int , @TotalAmount money, @InstallmentAmount money , @BalanceAmount money , @InstallmentDate nvarchar(20), @PaidDate nvarchar(20) , @PaymentOption nvarchar(20) , @CheqOrDDNo nvarchar(max) , @BankName nvarchar(50) , @BankBranch nvarchar(50) , @InstallmentDescription nvarchar(max) , @ChequeIsuuedDate nvarchar(20) , @PaymentDone nvarchar(10), @UpdateBy nvarchar(30), @UpdateDate nvarchar(30) , @UpdateTime nvarchar(30) ) AS INSERT INTO Installments ( InstallmentNo, AdmsnID , TotalAmount, InstallmentAmount, BalanceAmount, InstallmentDate, PaidDate , PaymentOption , CheqOrDDNo , BankName , BankBranch , InstallmentDescription , ChequeIsuuedDate, PaymentDone, UpdateBy, UpdateDate , UpdateTime ) VALUES ( @InstallmentNo, @AdmsnID , @TotalAmount, @InstallmentAmount, @BalanceAmount, @InstallmentDate, @PaidDate , @PaymentOption , @CheqOrDDNo , @BankName , @BankBranch , @InstallmentDescription , @ChequeIsuuedDate, @PaymentDone, @UpdateBy, @UpdateDate , @UpdateTime ) some parameters may be extra ignore them or delete its fine Here is table definition for Admission Table /****** Object: Table [dbo].[Admission] Script Date: 07/20/2010 00:26:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO SET ARITHABORT ON GO CREATE TABLE [dbo].[Admission]( [AdmsnID] [int] IDENTITY(1,1) NOT NULL, [AdmissionID] AS ('S-'+CONVERT([nvarchar],[AdmsnID],(0))) PERSISTED NOT NULL, [EnquiryID] [nvarchar](max) NOT NULL, [Course] [nvarchar](10) NOT NULL, [Firstname] [nvarchar](30) NOT NULL, [Middlename] [nvarchar](30) NOT NULL, [LastName] [nvarchar](30) NOT NULL, [Name] AS (((([Firstname]+' ')+[Middlename])+' ')+[LastName]), [Gender] [nvarchar](10) NOT NULL, [Address] [nvarchar](max) NOT NULL, [ResiPh] [nvarchar](15) NULL, [MobileNo] [nvarchar](15) NOT NULL, [ZipCode] [nvarchar](15) NULL, [EMail] [nvarchar](30) NULL, [Preferbatch] [nvarchar](20) NULL, [FilePath] [nvarchar](max) NULL, [Remarks] [text] NULL, [EnquiryDate] [nvarchar](30) NULL, [UpdateBy] [nvarchar](30) NULL, [UpdateDate] [nvarchar](30) NULL, [UpdateTime] [nvarchar](30) NULL, CONSTRAINT [Pk_Admission] PRIMARY KEY CLUSTERED ( [AdmsnID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [unique_Admsn] UNIQUE NONCLUSTERED ( [AdmissionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO thanks for trying to help. its a very complicated problem
So if i understand it right, you have an admission that then has an entry in payments which itself creates entries in the installments tables according to a value in the payments table. The installments are then entered ahead of time, so a payment entry with 10 installments would instantly create 10 entries in the installment table. Please confirm or correct my assumptions and supply the table definition for the payment table.
You can't write triggers like that. You would need to get rid of all the variable assignments and turn it into a single UPDATE statement (actually a lot simpler than the code you have). Why do it in a trigger at all though? I would modify the stored procedure instead. Triggers are generally best avoided, except as a last resort.
William u r right only thing is when installments table is automatically created 10 installments row for 10 installments and some values like Installments no and id are auto generated. while values for installmentAmount and Balanceamount are added later by user. when user add installmentAmount value its calculates the balance Amount from total amount and inserts it automattically but problem is suppose last value in installmentAmount column is 9500 as in second image when entered value less than 9500 suppose 2000(as in image) u can see that Balnce amount is properly calculated but next installmentAmount value in new added row by DeleteORAddtrg trigger (row no 8) gives null. but when trying to store this value in another column value is displayed and this null problem happens alternately sorry i forgot enter payment table definition CREATE TABLE Payment ( PayID int IDENTITY(1,1) CONSTRAINT Pk_Payment PRIMARY KEY, PaymentID AS ('PAY-' + CAST(PayID AS nvarchar)) PERSISTED CONSTRAINT unique_Payment UNIQUE NONCLUSTERED NOT NULL, AdmsnID int CONSTRAINT Fk_Payment_Admission FOREIGN KEY REFERENCES Admission(AdmsnID), Package nvarchar(50) NOT NULL, FeeAmount money NOT NULL, ManualDiscount char(1) NOT NULL, DiscountInCash money NULL, TotalFees money NOT NULL, No_Of_Installments int NOT NULL, Paid_Installments int NOT NULL, PaymentDate nvarchar(20) NOT NULL, PaymentOption nvarchar(20) NOT NULL, PaidAmount money NOT NULL, Balance money NOT NULL, ChequeNo nvarchar(max) NULL, BankName nvarchar(50) NULL, BankBranch nvarchar(50) NULL, ChequeIsuuedDate nvarchar(20) NULL, PaymentDescription nvarchar(max) NULL, UpdateBy nvarchar(30) NULL, UpdateDate nvarchar(30) NULL, [UpdateTime] [nvarchar](30) NULL ) u can delete uneeded rows i think that - updating installment amount value and that reflects all below rows - this logic in UpdateInstallmentAmount Sproc may interfere deleteORAddtrg Trigger
Thanks William I will adapt your way and if any problem comes i 'll post it I would like to mention that IF CAST(@PreviousInstallmentDate AS Datetime) > CAST(@InstallmentDate AS Datetime) I have assigned it value using SELECT @PreviousInstallmentDate = InstallmentDate,@PreviousPaidDate = PaidDate FROM Installments WHERE AdmsnID = @AdmsnID AND InstallmentNo = (@InstallmentNo - 1) using proc deinition CREATE PROC UpdateInstallmentAmount @AdmsnID int, @NewInstallment money, @InstallmentNo int, @InstallmentDate nvarchar(20), @PaidDate nvarchar(20) AS --BEGIN TRY --BEGIN TRANSACTION TR1 DECLARE @PreviousInstallmentDate nvarchar(20) DECLARE @PreviousPaidDate nvarchar(20) DECLARE @PresentInstallment money DECLARE @PresentBalance money DECLARE @Nextinstallment money DECLARE @MaxInstallment int SELECT @PresentInstallment = InstallmentAmount,@PresentBalance = BalanceAmount FROM Installments WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo SELECT @Nextinstallment = InstallmentAmount FROM Installments WHERE AdmsnID = @AdmsnID AND InstallmentNo = (@InstallmentNo + 1) SELECT @PreviousInstallmentDate = InstallmentDate,@PreviousPaidDate = PaidDate FROM Installments WHERE AdmsnID = @AdmsnID AND InstallmentNo = (@InstallmentNo - 1) BEGIN UPDATE Installments SET InstallmentDate = @InstallmentDate WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo END IF CAST(@PreviousInstallmentDate AS Datetime) > CAST(@InstallmentDate AS Datetime) BEGIN RAISERROR('Date entered Should not be before today',16,1) END ELSE BEGIN UPDATE Installments SET PaidDate = @PaidDate WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo END UPDATE Installments SET InstallmentAmount = @NewInstallment WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo DECLARE @PreviousBalance money SELECT @PreviousBalance = BalanceAmount FROM Installments WHERE AdmsnID = @AdmsnID AND (InstallmentNo = @InstallmentNo - 1) IF @PreviousBalance is null BEGIN RAISERROR('Trying to enter wrong Installment',16,1) END ELSE IF @PreviousBalance = '' BEGIN RAISERROR('Trying to enter wrong Installment',16,1) END ELSE BEGIN IF (@PreviousBalance - @NewInstallment) >= 0 BEGIN UPDATE Installments SET BalanceAmount = @PreviousBalance - @NewInstallment WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo END ELSE BEGIN RAISERROR('Exceeding balance Amount',16,1) END END SELECT @PreviousBalance = BalanceAmount FROM Installments WHERE AdmsnID = @AdmsnID AND (InstallmentNo = @InstallmentNo - 1) PRINT @PresentInstallment PRINT @Nextinstallment DECLARE @NewBalance money IF (@NewInstallment <> @PresentInstallment) AND (@PresentInstallment < @NewInstallment) BEGIN SET @NewBalance = (@PreviousBalance - @NewInstallment) WHILE @NewBalance > 0 BEGIN SELECT @Nextinstallment = InstallmentAmount FROM Installments WHERE AdmsnID = @AdmsnID AND InstallmentNo = (@InstallmentNo + 1) IF @NewBalance > @Nextinstallment BEGIN UPDATE Installments SET BalanceAmount = @NewBalance WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo END ELSE BEGIN UPDATE Installments SET BalanceAmount = @NewBalance WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo UPDATE Installments SET InstallmentAmount = @NewBalance,BalanceAmount = 0 WHERE AdmsnID = @AdmsnID AND InstallmentNo = (@InstallmentNo + 1) SET @NewBalance = 0 END SET @InstallmentNo = @InstallmentNo + 1 SELECT @PresentInstallment = InstallmentAmount,@PresentBalance = BalanceAmount FROM Installments WHERE AdmsnID = @AdmsnID AND InstallmentNo = @InstallmentNo IF (@NewBalance - @PresentInstallment) > 0 BEGIN SET @NewBalance = (@NewBalance - @PresentInstallment) END SET @Nextinstallment = @PresentInstallment END END IF (@NewInstallment <> @PresentInstallment) AND (@PresentInstallment > @NewInstallment) BEGIN SET @NextInstallment = (@NextInstallment + (@PresentInstallment - @NewInstallment)) --PRINT @NextInstallment UPDATE Installments SET InstallmentAmount = @NextInstallment WHERE AdmsnID = @AdmsnID AND InstallmentNo = (@InstallmentNo + 1) END COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION TR1 END CATCH pls tell me if its wrong way 2 assign value it can be executed as EXEC UpdateInstallmentAmount 1,5000.00,2,'2/07/2010',null If this works for me i'll vote this as answer. And @newBalance is sassigned 0 ALTER TRIGGER DeleteORAddInstallment ON Installments AFTER UPDATE AS DECLARE @AdmsnID int DECLARE @PresentInstallmentNo int DECLARE @NewInstallment money DEClARE @No_Of_Installments int DECLARE @PreviousBalance money DECLARE @InstallmentNo int DECLARE @PresentBalance money SELECT @AdmsnID = AdmsnID, @PresentInstallmentNo = InstallmentNO, @NewInstallment = InstallmentAmount, @PresentBalance = BalanceAmount FROM INSERTED SELECT @PreviousBalance = BalanceAmount FROM Installments WHERE AdmsnID = @AdmsnID AND InstallmentNo = (@PresentInstallmentNo - 1) DECLARE @LastInstallment int DECLARE @LastBalance money DECLARE @NewBalance money DECLARE @TotalAmount money DECLARE @PaidAmount money SELECT @LastInstallment = MAX(InstallmentNo) FROM Installments WHERE AdmsnId = @AdmsnID SELECT @LastBalance = BalanceAmount, @TotalAmount = TotalAmount FROM Installments WHERE AdmsnId = @AdmsnID AND InstallmentNo = @LastInstallment IF @LastBalance > 0 BEGIN SET @NewBalance = 0 SET @PaymentDone= 'NotDone' SET @LastInstallment = @LastInstallment + 1 EXEC InsertInstallmentsProc @LastInstallment, @AdmsnID, @TotalAmount, @LastBalance, @NewBalance, @PaymentDone UPDATE Payment SET Paid_Installments = @LastInstallment WHERE AdmsnID = @AdmsnID END And i'll do calulations using inbuilt functions like SUM() Thanks for this corrections And thanks for that tally table use
As I mentioned, you really need to try and get rid of that trigger. **Each and every time** that you run an update on `dbo.Installments` the trigger fires (6 times minimum if I read your code correctly). Please try to find another way of doing what you want to do. As it is, you will experience concurrency problems when the system is under load. Also try to comment your code to say what you are doing at each step in your code, so that you (or someone else) can maintain it later.
I want to execute the TRIGGER DeleteORAddInstallment but I am receiving the belwo error, please advise. Msg 208, Level 16, State 6, Procedure DeleteORAddInstallment, Line 44 Invalid object name 'DeleteORAddInstallment'.
1 Person is following this question.
