Avatar of jspc
jspc
Flag for Australia

asked on 

SQL Trigger Not Working

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.
DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Raja Jegan R

8/22/2022 - Mon