troubleshooting Question

SQL Trigger Not Working

Avatar of jspc
jspcFlag for Australia asked on
DatabasesMicrosoft SQL ServerSQL
23 Comments1 Solution118 ViewsLast Modified:
Hello, I have the below SQL Trigger which is not 'firing'. SQL creates the trigger successfully.
The trigger should only 'fire' when a transactions margin is less than 20 (20%).

I think the issue is in this line: If (Select (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 from inserted) < 20

Below is the code:

USE ABC123
GO
If EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig_mail' AND type = 'TR')
DROP TRIGGER trig_mail
GO
CREATE TRIGGER trig_mail
ON TRANSDETAILS
FOR INSERT, UPDATE
AS

declare @AccountingRef varchar(100)
declare @Description varchar(100)
declare @body_msg varchar(1000)

SELECT @AccountingRef = AccountingRef FROM INSERTED
SELECT @Description = Description FROM INSERTED
SELECT @body_msg = 'A product code with Tax Invoice Number & Product Description: ' + @AccountingRef + @Description +
'has been sold to a Customer where the margin is less than 20%. Please review this transaction to maintain your profit margins.
You may need to issue a Credit Adjustment Note to correct the cost or sell price, then re-invoice.'

If (Select (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 from inserted) < 20
Begin

Your help would be much appreciated.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 23 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 23 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros