Start Free Trial

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

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

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?

ASKER

I get this error now when trying to do a sales transaction.

User generated image

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

Yes, the error is because it was master..xp_sendmail instead of master.xp.sendmail.
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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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@queryingsql.com',
@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'

Open in new window

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

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'

User generated image

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

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/

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

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

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.

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

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.

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

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

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.

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

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

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

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.

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

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

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

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.