I want to be able to create a SQL trigger on my database to email me when a transaction is made and the margin is less than 20%.
A previous IT professional had created this trigger (which I think is the fundamentals) however it was never finished and I am unsure how to finish it off so it runs then emails me.
Are you able to help?
The example trigger is:
If EXISTS (SELECT name FROM sysobjects
WHERE name = ‘trig_mail’ AND type = ‘TR’)
DROP TRIGGER trig_mail
CREATE TRIGGER trig_mail
FOR INSERT, UPDATE, DELETE
Select from inserted where (ProductCode <> ‘Freight’)
@percentage = (Localcost-LocalUnitPrice) / (LocalUnitPrice*100)
If (percentage < 20)
Exec master.xp.sendmail ‘EmailID’, ‘EmailMessage’
When I run the query I get:
Msg 102, Level 15, State 1, Procedure trig_mail, Line 6
Incorrect syntax near '@percentage'.