SQL Trigger

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'.
jspcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Assuming LocalCost, LocalUnitPrice are columns in the TRANSDETAILS table, fixed the syntax issues in your code..
USE testdatabase
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, DELETE
AS

If (Select (Localcost-LocalUnitPrice)/(LocalUnitPrice*100) from inserted where ProductCode <> 'Freight') < 20
Begin
Exec master.xp.sendmail 'EmailID', 'EmailMessage'
END
GO

Open in new window

jspcAuthor Commented:
Thanks.
Do I need to configure any email settings EG enter an email address where I want the message sent to?
Is there anything else I need to do?
jspcAuthor Commented:
I get this error now when trying to do a sales transaction.
Untitled.png
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, you would need to configure Database Mail if not already configured in your SQL Server instance..
If already configured, then please test whether you are able to receive test mails or not. If not configured, then you can configure as mentioned below.
https://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-server/

Exec master.xp.sendmail 'EmailID', 'EmailMessage'

Open in new window


Missed noticing xp.sendmail earlier..
Kindly use msdb.dbo.sp_send_dbmail instead of the older master.xp.sendmail procedure..sample script available in the below link..
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, the error is because it was master..xp_sendmail instead of master.xp.sendmail.
That's why you got the above error.
jspcAuthor Commented:
I've just realised, I am using SQL Express and it doesn't support mail
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I am using SQL Express and it doesn't support mail

Not exactly, using Express option to enable Database mail is not explicit and you would need to do some configurations as mentioned below:
http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/02/sending-mail-using-sql-server-express-edition/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jspcAuthor Commented:
Thank you for your wonderful help!
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome, glad to assist!!
jspcAuthor Commented:
Just a question about setting up the database mail on express. Do I need to run each of those scripts as they are?
I'm not sure what to use in these areas:

@account_name = 'MailTest',
@username='umashankar@queryingsql.com',
@password='password',
@mailserver_name = 'mail.queryingsql.com'
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Nope, script under each and every step should be executed in a single batch, for eg.. after replacing all values to suit your requirement execute the below code once together
 EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailTest',
@description = 'Sent Mail using MSDB',
@email_address = 'umashankar@queryingsql.com',
@display_name = 'umashankar',
@username='umashankar@queryingsql.com',
@password='password',
@mailserver_name = 'mail.queryingsql.com'

Open in new window

Doug BishopDatabase DeveloperCommented:
I would change your CREATE TRIGGER code to:
CREATE TRIGGER trig_mail
ON TRANSDETAILS
FOR INSERT, UPDATE
AS
...

Open in new window

There will be no inserted table for deletes so it is a waste of resources to call the trigger when you are deleting data..
jspcAuthor Commented:
Thanks for that tip!
jspcAuthor Commented:
Hello, I have followed the steps to configure the mail setup for SQL Express. When I get to the last step to test email it says "Mail Queued" but I don't received any email.

exec msdb.dbo.sp_send_dbmail @profile_name = 'MailTest', @recipients = 'me@myemail.com.au', @subject = 'Mail Test', @body = 'Mail Sent Successfully', @body_format = 'text'

Capture.JPG
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> When I get to the last step to test email it says "Mail Queued" but I don't received any email.

If the mail was queued properly, then the mail was sent from SQL server to the SMTP Server successfully..
Kindly check whether the SMTP server have any blocking or restrictions or mails from this server or not..
jspcAuthor Commented:
Im not sure how to do that. I'm using a gmail account.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Okay, there might be couple of factors..
Try installing .Net Framework 3.5 as required..
http://expressnetsolutions.com/sqldch/?p=683
And couple of general testing methodology to fix Database Mail.
http://www.midnightdba.com/DBARant/complete-troubleshooting-guide-for-sql-server-databasemail-dbmail/
jspcAuthor Commented:
I managed to view the log in SQL Express and it says:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2018-12-19T14:06:23). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Must issue a STARTTLS command first. k191sm19443460pgd.9 - gsmtp). )
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
That means Gmail requires you to use SSL connections,
Then you would need to enable ssl for your SMTP server by adding @enablSSL option in the first script:
Now, you can execute the below script after replacing appropriate values to get SSL enabled.
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_name = 'MailTest',
@description = 'Sent Mail using MSDB',
@email_address = 'umashankar@queryingsql.com',
@display_name = 'umashankar',
@username='umashankar@queryingsql.com',
@password='password',
@mailserver_name = 'mail.queryingsql.com',
@enable_ssl = 1

Open in new window

jspcAuthor Commented:
Ok thanks.
I have run the script with my appropriate values.
I have also stopped and started the service again. Tested but get this error:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2018-12-19T14:23:31). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at). )

My Firewall is currently off.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
jspcAuthor Commented:
Thanks.
It's working! Your the best!

In your link it was the below step. Switching on fixed it.

solution for case 2: go to security settings at the followig linkhttps://www.google.com/settings/security/lesssecureapps and enable less secure apps . So that you will be able to login from all apps.

Thanks again.
jspcAuthor Commented:
Hello again,

I have been testing this trigger. It appears to be ‘firing’ all the time. It should only ‘trigger’ if the margin is less than 20%.

I also have a field in the same table called “AccountingRef”.
Am I able to display in the body of the email the Accounting Ref so I know which effected transaction it relates too?

USE ABC
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

If (Select (LocalUnitPrice-LocalCost)/(LocalUnitPrice*100) from inserted ) < 20
Begin

EXEC msdb.dbo.sp_send_dbmail  
      @recipients = 'abm123@gmail.com',  
    @body = 'A product has been sold to a Customer where the margin is less than 20%',  
    @subject = 'Automated Success Message - Margin Warning' ;  

END
GO
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome, so your email issue is now fixed out right..

>> It appears to be ‘firing’ all the time. It should only ‘trigger’ if the margin is less than 20%.

Just reviewed the code you have originally shared, I think it should be changed from
If (Select (LocalUnitPrice-LocalCost)/(LocalUnitPrice*100) from inserted ) < 20

Open in new window

to
If (Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20

Open in new window

to get the margin percentage..

>> I also have a field in the same table called “AccountingRef”.

Sure, you can just declare two variables called as @AccountingRef and @body_msg, pass values from INSERTED to @AccountingRef and then dynamically concatenate your message for @body_msg variable.
Then pass it to @body parameter of the procedure..
 @body = @body_msg,

Open in new window

jspcAuthor Commented:
Yes the email issue is working now.

Ok, I have fixed the code.

Sorry I don't really understand the AccountingRef part. Are you able to provide the code?

Thank you.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Sure, try this..
USE ABC
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 @body_msg varchar(1000)

SELECT @AccountingRef = AccountingRef FROM INSERTED
SELECT @body_msg = 'A product with AccountingRef: ' + @AccountingRef + ' has been sold to a Customer where the margin is less than 20%'

If (Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20
Begin

EXEC msdb.dbo.sp_send_dbmail  
      @recipients = 'abm123@gmail.com',  
    @body = @body_msg,  
    @subject = 'Automated Success Message - Margin Warning' ;  

END
GO 

Open in new window

jspcAuthor Commented:
Excellent - thank you. You've be so helpful.

I've just tested the amended code, however now it won't trigger (fire) at all. Something must be a miss ...

Is there a bracket missing?

(Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Is there a bracket missing?

I don't think so, If there is a syntax issue, you won't be able to create the trigger at all..

>> (Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20

Might be the logics across these columns causing the trigger not to send email.
Kindly give 2 sets of sample values for LocalUnitPrice and LocalCost columns with the below case so that the above can be tweaked out
1. Values for which trigger should be sent
2. Values for which trigger shouldn't send..
jspcAuthor Commented:
OK

Example to not fire:

LocalUnitPrice $100.00
LocalCost $14.97

So Margin above is 85.02%.
Trigger should NOT fire.

Example it should fire

LocalUnitPrice $10.00
LocalCost $14.97

So Margin above is -49.78%.
Trigger should fire.

Trigger should only fire when margin is below 20%.

Hope that helps.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Both
If (Select (LocalUnitPrice-LocalCost)*100/LocalUnitPrice from inserted ) < 20

Open in new window

and
(Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20 

Open in new window

should trigger the alert for the second case, kindly check..
jspcAuthor Commented:
Hello,

1. Have tested the first line - trigger didn't fire

2. Tried to run second script and got this error message in SQL:
Msg 102, Level 15, State 1, Procedure trig_mail, Line 16
Incorrect syntax near '<'.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Sorry, missed IF at the beginning in second script..
Okay, is there any possibility of having NULL AccountingRef column which can cause some issues..
USE ABC
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
AFTER INSERT, UPDATE
AS

declare @AccountingRef varchar(100)
declare @body_msg varchar(1000)

SELECT @AccountingRef = AccountingRef FROM INSERTED
SELECT @body_msg = 'A product with AccountingRef: ' + ISNULL(@AccountingRef, '') + ' has been sold to a Customer where the margin is less than 20%'

If (Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20
Begin

EXEC msdb.dbo.sp_send_dbmail  
      @recipients = 'abm123@gmail.com',  
    @body = @body_msg,  
    @subject = 'Automated Success Message - Margin Warning' ;  

END
GO 

Open in new window

jspcAuthor Commented:
No this is a NON NULL field.

I've just tested the above code and it still won't trigger when I sell a product under 20%.

I might just leave it - thanks for your time. I really appreciate it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.