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.