Ok, I have done a lot of work on this and it has moved into a different direction. Problem I am facing now is that the validation is failing on the amount. I have a credit limit of say $100 and if I try to process an order of $99.95, it raises the error. Any ideas why? I have put the sp and new trigger in. ALTER TRIGGER [dbo].[CREDIT_VERIFICATION_SO] ON [dbo].[InvNum] for INSERT, UPDATE AS BEGIN DECLARE @accid INT SELECT @accid = AccountID FROM inserted EXEC __credit_check_so @accid DECLARE @TRIGGER INT SELECT @TRIGGER = Count(*) FROM INSERTED INNER JOIN __Credit_Verification C ON DCLINK = AccountID WHERE Credit_Available - ( OrdTotIncl + C.TOTAL_Processed ) < 0 IF @TRIGGER > 0 BEGIN RAISERROR ('This will exceed the credit available which is not allowed',16,1) ROLLBACK TRANSACTION END TRUNCATE TABLE __Credit_Verification END ALTER PROCEDURE [dbo].[__Credit_check_so] @accid int AS INSERT INTO __Credit_Verification (DCLink) SELECT DISTINCT dclink FROM Client where dclink = @accid SELECT a.accountid, isnull(a.ordtotincl,0) - isnull(b.invtotincl,0) AS "Parcially Processed Orders" INTO #PP FROM invnum b INNER JOIN invnum a ON b.AutoIndex = a.OrigDocID WHERE a.doctype = '4' AND a.DocState > '1' AND a.AccountID = @accid GROUP BY a.accountid, a.ordtotincl, b.invtotincl HAVING Sum(a.ordtotincl - b.invtotincl) > 0 UPDATE __Credit_Verification SET Partcially_Processed = [Parcially Processed Orders] FROM __Credit_Verification INNER JOIN #PP ON __Credit_Verification.dclink = #pp.AccountID SELECT accountid, Sum(isnull(ordtotincl,0)) AS "Unprocessed Orders" INTO #FP FROM invnum WHERE doctype = '4' AND DocState = '1' AND OrigDocID = 0 and AccountID = @accid GROUP BY accountid, OrigDocID HAVING Sum(isnull(ordtotincl,0)) > 0 UPDATE __Credit_Verification SET Fully_Processed = [Unprocessed Orders] FROM __Credit_Verification INNER JOIN #FP ON __Credit_Verification.dclink = #fp.AccountID Declare @null money select @null = 0 update C set Credit_Available = @null from __Credit_Verification C where Credit_Available is null and dclink = @accid update C set Partcially_Processed = @null from __Credit_Verification C where Partcially_Processed is null and dclink = @accid update C set Fully_Processed = @null from __Credit_Verification C where Fully_Processed is null and dclink = @accid UPDATE __Credit_Verification SET TOTAL_Processed = Partcially_Processed + Fully_Processed SELECT dclink, Sum(isnull(credit_limit,0) - isnull(dcbalance,0)) AS "Credit Available" INTO #CA FROM client where dclink = @accid GROUP BY dclink UPDATE __Credit_Verification SET Credit_Available = [Credit Available] FROM __Credit_Verification INNER JOIN #ca ON __Credit_Verification.dclink = #ca.DCLink
It's a little difficult to analyze without knowing more about your process and tables, but I have a few thoughts. 1. Unless the trigger is disabled, it is definitely executing for any insert or update of the dbo.INVNUM table. Is that the correct schema and table? 2. The WHERE and HAVING clauses of the "IF EXISTS" statement in the trigger could be accidentally restricting too much. For example, you used a left join (and again later) but then checked a column in that table in the HAVING clause. That effectively treats it like an inner join. 3. I understand you believe you will only process 1 row at a time. That said, from a trigger design perspective, Sean does have a valid concern about the trigger processing multiple values. It's easy to resolve in your trigger, though. Just set the credit limit to the MAX of OrdTotInclDex. Please let us know if any of that helps. If it does, please mark the answer as correct. If not, please provide more details about your process and tables so we can provide better assistance.