asked on 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)
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'.
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)
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'.
DatabasesMicrosoft SQL ServerSQL
Last Comment
jspc
ASKER
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?
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?
ASKER
I get this error now when trying to do a sales transaction.
![Untitled.png]()
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/
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
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'
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
Yes, the error is because it was master..xp_sendmail instead of master.xp.sendmail.
That's why you got the above error.
That's why you got the above error.
ASKER
I've just realised, I am using SQL Express and it doesn't support mail
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you for your wonderful help!
Welcome, glad to assist!!
ASKER
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@quer
@password='password',
@mailserver_name = 'mail.queryingsql.com'
I'm not sure what to use in these areas:
@account_name = 'MailTest',
@username='umashankar@quer
@password='password',
@mailserver_name = 'mail.queryingsql.com'
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'
I would change your CREATE TRIGGER code to:
CREATE TRIGGER trig_mail
ON TRANSDETAILS
FOR INSERT, UPDATE
AS
...
ASKER
Thanks for that tip!
ASKER
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]()
exec msdb.dbo.sp_send_dbmail @profile_name = 'MailTest', @recipients = 'me@myemail.com.au', @subject = 'Mail Test', @body = 'Mail Sent Successfully', @body_format = 'text'
>> 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..
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..
ASKER
Im not sure how to do that. I'm using a gmail account.
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/
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/
ASKER
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). )
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). )
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.
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
ASKER
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.
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.
Okay, then it might be due to your security settings. Kindly try the below.
https://www.smarterasp.net/support/kb/a1546/send-email-from-gmail-with-smtp-authentication-but-got-5_5_1-authentication-required-error.aspx
https://www.smarterasp.net/support/kb/a1546/send-email-from-gmail-with-smtp-authentication-but-got-5_5_1-authentication-required-error.aspx
ASKER
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.
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.
ASKER
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)
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
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)
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
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
>> 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..
>> 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
If (Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20
>> 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,
ASKER
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.
Ok, I have fixed the code.
Sorry I don't really understand the AccountingRef part. Are you able to provide the code?
Thank you.
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
ASKER
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
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
>> 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
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..
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
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..
ASKER
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.
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.
Both
If (Select (LocalUnitPrice-LocalCost)*100/LocalUnitPrice from inserted ) < 20
(Select ((LocalUnitPrice-LocalCost)/LocalUnitPrice) *100 from inserted ) < 20
ASKER
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 '<'.
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 '<'.
Sorry, missed IF at the beginning in second script..
Okay, is there any possibility of having NULL AccountingRef column which can cause some issues..
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
ASKER
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.
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.
Open in new window