Avatar of jspc
jspc
Flag for Australia 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) / (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'.
DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
jspc

8/22/2022 - Mon
Raja Jegan R

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

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?
jspc

ASKER
I get this error now when trying to do a sales transaction.
Untitled.png
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Raja Jegan R

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 R

Yes, the error is because it was master..xp_sendmail instead of master.xp.sendmail.
That's why you got the above error.
jspc

ASKER
I've just realised, I am using SQL Express and it doesn't support mail
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Raja Jegan R

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jspc

ASKER
Thank you for your wonderful help!
Raja Jegan R

Welcome, glad to assist!!
jspc

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@queryingsql.com',
@password='password',
@mailserver_name = 'mail.queryingsql.com'
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Raja Jegan R

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

D B

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..
jspc

ASKER
Thanks for that tip!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jspc

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
Raja Jegan R

>> 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..
jspc

ASKER
Im not sure how to do that. I'm using a gmail account.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Raja Jegan R

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/
jspc

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). )
Raja Jegan R

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jspc

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.
Raja Jegan R

jspc

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
jspc

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)/(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 R

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

jspc

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Raja Jegan R

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

jspc

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)/LocalUnitPrice) *100 from inserted ) < 20
Raja Jegan R

>> 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..
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
jspc

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.
Raja Jegan R

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..
jspc

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 '<'.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Raja Jegan R

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

jspc

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.