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:
If EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig_mail' AND type = 'TR')
DROP TRIGGER trig_mail
CREATE TRIGGER trig_mail
FOR INSERT, UPDATE
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
Your help would be much appreciated.