troubleshooting Question

SQL Trigger

Avatar of jspc
jspcFlag for Australia asked on
DatabasesMicrosoft SQL ServerSQL
33 Comments1 Solution122 ViewsLast Modified:
Hello,

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:

USE testdatabase
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, DELETE
AS
Select from inserted where (ProductCode <> ‘Freight’)
@percentage = (Localcost-LocalUnitPrice) / (LocalUnitPrice*100)
If (percentage < 20)
Begin
Exec master.xp.sendmail ‘EmailID’, ‘EmailMessage’
END
GO

When I run the query I get:

Msg 102, Level 15, State 1, Procedure trig_mail, Line 6
Incorrect syntax near '@percentage'.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 33 Comments.
Start Free Trial
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 33 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