asked
trigger assistance
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
If you are inserting into the INVNUM table your trigger is firing, period. What is likely happening is that the if condition is not met and therefore the rest of the code doesn't execute. You have a major flaw in your code. You are using scalar values and assuming there can't ever be more than 1 row being inserted. This may be true from the application perspective but this is very dangerous. You should rethink how to make this work correctly when the inserted table has multiple rows.
You can't blindly accept that will always be the case. There will be times when you have to manually insert data or the process will change. A consultant friend of mine actually saw an entire business fold because they had triggers like this that couldn't handle multiple row operations. It isn't that difficult to write your code to handle that.
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.
I don't see any insert into invnum. One thing you could do to test would be to write a test case that does the same validation as the trigger but without inserting into the table. Just select values based on your inputs and see what you get. Sorry, but it's difficult to debug triggers without access to the system and data, so I'm trying to come up with suggestions you could try.
Also, I'm confused about the handling of __Credit_Verification. It is populated in the procedure but truncated at the end of the trigger. Why?
18 People are following this question.
