<div>
Okay, try the slightly modified code below.. 

<pre><code class="code-1 nolinks" id="code-20-42760220-1">IF (Select CASE WHEN (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 &lt; 20 THEN 1 ELSE 0 END from inserted) = 1
</code></pre>
</div>

<div>
Hello, Just tried that new code. Still wont work. 
 
Would the below 'declare' be causing the issue? 
 
declare @AccountingRef varchar(100) 
declare @Description varchar(100) 
 
SELECT @AccountingRef = AccountingRef FROM INSERTED 
SELECT @Description = Description FROM INSERTED
</div>

<div>
Okay, try this one.. 

<pre><code class="code-10 nolinks" id="code-20-42760237-1">declare @AccountingRef varchar(100)
declare @Description varchar(100)
declare @margin bit
declare @body_msg varchar(1000)

SELECT @AccountingRef = AccountingRef,@Description = Description, @Margin = CASE WHEN (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 &lt; 20 THEN 1 ELSE 0 END FROM INSERTED
SELECT @body_msg = 'A product code with Tax Invoice Number &amp; Product Description: ' + @AccountingRef + @Description +
'has been sold to a Customer where the margin is less than 20%. Please review this transaction to maintain your profit margins.
You may need to issue a Credit Adjustment Note to correct the cost or sell price, then re-invoice.'

If @Margin = 1
Begin
</code></pre>
</div>

<div>
Thanks. 
 
No still wont work. 
Is was working but I can't remember now at what point it stopped working as we have made so many changes. The time it was working though it would email whether it was above or below 20%
</div>

<div>
Okay, just checking are you able to send test mails via msdb.dbo.sp_send_dbmail procedure.. 
if you aren't receiving mails, then the trigger might be working but your mail service might be down or something..
</div>

<div>
Yes test mails are working fine via msdb.sp_send_dbmail 
 
exec msdb.dbo.sp_send_dbmail @profile_name = 'DBMail', @recipients = 'abc123@gg.com.au', @subject = 'Mail Test', @body = 'Mail Sent Successfully', @body_format = 'text'
</div>

<div>
Would it be quicker and easier if I uploaded a copy of the database? 
Or do you Team Viewer in at all? 
 
Test database attached. 
<a href="https://filedb.experts-exchange.com/incoming/2018/12_w51/1407614/Test.zip" target="_blank" class="file-inline" title="Test.zip (832 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Test.zip</a>
</div>

Test.zip

<div>
Tested with your database, Logic works fine and passes that condition, can you provide me the entire script you are using for trigger to verify once..
</div>

<div>
USE EvaluationCompany8AU 
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 @Description varchar(100) 
declare @margin bit 
declare @body_msg varchar(1000) 
 
SELECT @AccountingRef = AccountingRef, @Description = Description, @Margin = CASE WHEN (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 &lt;&nbsp;20 THEN 1 ELSE 0 END FROM INSERTED 
SELECT @body_msg = 'A product code with Tax Invoice Number &amp;&nbsp;Product Description: ' + @AccountingRef + @Description + 
'has been sold to a Customer where the margin is less than 20%. Please review this transaction to maintain your profit margins. 
You may need to issue a Credit Adjustment Note to correct the cost or sell price, then re-invoice.' 
 
If @Margin = 1 
Begin 
 
EXEC msdb.dbo.sp_send_dbmail &nbsp; 
&nbsp; &nbsp; @recipients = 'xxxxxxx', &nbsp; 
&nbsp; &nbsp; @body = @body_msg, &nbsp; 
&nbsp; &nbsp; @subject = 'Automated Success Message - ABM Margin Warning'; &nbsp; 
 
END 
GO
</div>

<div>
Okay. I don't know, why no one has started at the beginning... 
 

<blockquote>
The trigger should only 'fire' when a transactions margin is less than 20 (20%).
</blockquote>
A trigger in SQL Server is fired once per statement. Not per row or based on a conditional. This means that you need to write your trigger to work on one row as well to work on a set. Thus triggers provide the virtual tables INSERTED and DELETED. Your trigger and the others are flawed. 
 
So you need to loop over INSERTED or do set-based operations. E.g. 
 

<pre><code class="code-10 nolinks" id="code-20-42760393-1">CREATE TRIGGER trig_mail
ON TRANSDETAILS
FOR INSERT, UPDATE
AS
SET NOCOUNT ON;

DECLARE @MESSAGE_BODY NVARCHAR(MAX) = N'A product code with Tax Invoice Number &amp; Product Description: @AccountingRef @Description has been sold to a Customer where the margin is less than 20%. Please review this transaction to maintain your profit margins. You may need to issue a Credit Adjustment Note to correct the cost or sell price, then re-invoice.';
DECLARE @MESSAGE_RECIPIENTS NVARCHAR(MAX) = N'';
DECLARE @MESSAGE_SUBJECT NVARCHAR(MAX) = N'';
DECLARE @NO_ERROR INT = 0;

DECLARE @AccountingRef NVARCHAR(100);
DECLARE @Body NVARCHAR(MAX);
DECLARE @Description NVARCHAR(100);

DECLARE LowMarginCursor CURSOR FOR
 SELECT I.AccountingRef ,
 I.[Description]
 FROM INSERTED I
 WHERE ( I.LocalUnitPrice - I.LocalCost ) / ( 1.0 * I.LocalUnitPrice ) &lt; 0.2;

OPEN LowMarginCursor;
FETCH NEXT FROM LowMarginCursor
INTO @AccountingRef ,
 @Description;
WHILE ( @@FETCH_STATUS = @NO_ERROR )
BEGIN
 SET @Body = REPLACE(REPLACE(@MESSAGE_BODY, '@AccountingRef', @AccountingRef), '@Description', @Description);
 EXEC msdb.dbo.sp_send_dbmail @recipients = @MESSAGE_RECIPIENTS ,
 @body = @Body ,
 @subject = @MESSAGE_SUBJECT;
 FETCH NEXT FROM LowMarginCursor
 INTO @AccountingRef ,
 @Description;
END;

CLOSE LowMarginCursor;
DEALLOCATE LowMarginCursor;
GO
</code></pre>
 
CAVEAT: the mail call may fail. This would mean that your user maybe not be able to store their data. Here the common pattern is to store the values for the mails in a table and use a SQL Server Agent Job to send the mail. 
 
p.s. triggers should always start with SET NOCOUNT ON; And depending on your data types you may have an conversion issue. Thus use appropriate float arithmetic.
</div>

<div>
Hello, thanks for your input. I'll test your example. 
Can you please tell me where I insert my email?
</div>

<div>
Create a table MarginWarnings ( ID, AccountingRef, Description, DateCreated, DateSent ) with GETDATE() as default for DateCreated. Then let the trigger just insert the rows: 
 

<pre><code class="code-10 nolinks" id="code-20-42760407-1">CREATE TRIGGER trig_mail
ON TRANSDETAILS
FOR INSERT, UPDATE
AS
SET NOCOUNT ON;

INSERT INTO MarginWarnings ( AccountingRef ,
 [Description] )
 SELECT I.AccountingRef ,
 I.[Description]
 FROM INSERTED I
 WHERE ( I.LocalUnitPrice - I.LocalCost ) / ( 1.0 * I.LocalUnitPrice ) &lt; 0.2;
GO
</code></pre>
Put the cursor code into a stored procedure and run it as SQL Server Agent Job, e.g. every 5 minutes. 
 

<pre><code class="code-10 nolinks" id="code-20-42760407-2">CREATE PROCEDURE SendMarginWarnings
AS
 SET NOCOUNT ON;

 DECLARE @MESSAGE_BODY NVARCHAR(MAX) = N'A product code with Tax Invoice Number &amp; Product Description: @AccountingRef @Description has been sold to a Customer where the margin is less than 20%. Please review this transaction to maintain your profit margins. You may need to issue a Credit Adjustment Note to correct the cost or sell price, then re-invoice.';
 DECLARE @MESSAGE_RECIPIENTS NVARCHAR(MAX) = N'';
 DECLARE @MESSAGE_SUBJECT NVARCHAR(MAX) = N'';
 DECLARE @NO_ERROR INT = 0;

 DECLARE @AccountingRef NVARCHAR(100);
 DECLARE @Body NVARCHAR(MAX);
 DECLARE @ID INT;
 DECLARE @Description NVARCHAR(100);

 DECLARE LowMarginCursor CURSOR FOR
 SELECT W.ID ,
 W.AccountingRef ,
 W.[Description]
 FROM MarginWarnings W
 WHERE W.DateSent IS NULL;

 OPEN LowMarginCursor;
 FETCH NEXT FROM LowMarginCursor
 INTO @ID ,
 @AccountingRef ,
 @Description;
 WHILE ( @@FETCH_STATUS = @NO_ERROR )
 BEGIN
 SET @Body = REPLACE(REPLACE(@MESSAGE_BODY, '@AccountingRef', @AccountingRef), '@Description', @Description);

 EXECUTE msdb.dbo.sp_send_dbmail @recipients = @MESSAGE_RECIPIENTS ,
 @body = @Body ,
 @subject = @MESSAGE_SUBJECT;

 UPDATE MarginWarnings
 SET DateSend = GETDATE()
 WHERE ID = @ID;

 FETCH NEXT FROM LowMarginCursor
 INTO @ID ,
 @AccountingRef ,
 @Description;
 END;

 CLOSE LowMarginCursor;
 DEALLOCATE LowMarginCursor;
GO
</code></pre>
 
btw, benefit of this table: You can also present the margin warnings in your application. Depending on your use-case and workflow to some redesign. Sounds like you can need some state tracking here.
</div>

<div>
This is starting to sound complicated. I’m relative new to SQL and SQL Server. 
 
Are you able to provide the script to create the table? 
 
Do I then run this script once the new table is created? 
 
CREATE TRIGGER trig_mail 
ON TRANSDETAILS 
FOR INSERT, UPDATE 
AS 
SET NOCOUNT ON; 
 
INSERT INTO MarginWarnings ( AccountingRef , 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[Description] ) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT I.AccountingRef , 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;I.[Description] 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM &nbsp; INSERTED I 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE &nbsp;( I.LocalUnitPrice - I.LocalCost ) / ( 1.0 * I.LocalUnitPrice ) &lt;&nbsp;0.2; 
GO 
 
What do you mean ‘cursor code’? 
 
I am unsure how to create an “Agent Job” in SQL Server Express.
</div>

<div>
In this case, take your time to go these steps. It's not complicated. 
 
For a beginner, read Itzik Ben-Gan's <a href="http://tsql.solidq.com/" rel="ugc">T-SQL Fundamentals</a>. 
Then read about <a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017" rel="ugc">triggers</a>. 
 
By &quot;cursor code&quot; I mean the <a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-2017" rel="ugc">cursor</a> processing, 
 
Well, SQL Server Express has no SQL Server Agent. That's the reason why questions should be complete.. In this case you should explain you use-case and architecture. But you should consider scheduling the e-mail creation by using <a href="https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017" rel="ugc">SQLCMD</a> and Task Scheduler.
</div>

jspc

<div>
Sorry for the delay in follow ups as I got busy last couple of days.. 
Okay, we can go with the approach suggested by ste5an above or else you can try the below approach as well: 
1. Create a Windows Task Scheduler job to execute once every 1 hour( or your preferred interval) to hit the database to see how many transactions were performed between the last alerted time to current time and send a mail. 
Note: Suggested to use Windows Task scheduler instead of SQL Server Agent since you have Express edition. 
2. Send a mail with the list of users or transactions so that the users can follow up accordingly. 
 
Reason, I've suggested to go with Job instead of Trigger is that lets say in worst case there were 100 or more transactions done within 1 hour with profit margin less than 20%, then you would have received 100+ mails so it would be difficult to follow up each and every mail to track the Accounting Ref no.. 
Instead running a Task scheduler job will send a single mail with all those 100+ Accounting Ref no in a single shot which would be easier for users to follow up closely. 
Kindly let me know whether you think this solution looks good or not so that we can work together accordingly.
</div>

<div>
That sounds like a great plan. 
 
Can it only email those under a certain percentage eg 20%?
</div>

<div>
Okay, if so, then kindly follow the 
<blockquote>
Automate Backups Using SQL Server Management Studio Express and Windows Task Scheduler
</blockquote>
 section of the below link and then replace the *.sql script with the below script after replacing the email addresses and table names. 
<a href="https://www.reliasoft.com/sql-server-express-backup-instructions" rel="ugc">https://www.reliasoft.com/sql-server-express-backup-instructions</a> 

<pre><code class="code-1 nolinks" id="code-20-42762977-1">EXEC msdb.dbo.sp_send_dbmail
@recipients=N'&lt;recipient_email_addresses&gt;',
@copy_recipients=N'&lt;copy_email_addresses&gt;',
@body=@MESSAGE_BODY,
@subject =@MESSAGE_SUBJECT,
@query = 'SELECT AccountingRef, Description, LocalUnitPrice, LocalCost, (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 Margin FROM ur_Table WHERE datetime_col &gt; dateadd(hh, datediff(hh, 0, getdate()),0)',
@query_attachment_filename= 'File_List',
@attach_query_result_as_file = 1 ; 
</code></pre>
</div>

<div>
Just can't see in the script the 20%
</div>

<div>
Sorry, missed it.. 
Change Line# 6 in the above script to this.. 

<pre><code class="code-1 nolinks" id="code-20-42762981-1">@query = 'SELECT AccountingRef, Description, LocalUnitPrice, LocalCost, (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 Margin FROM ur_Table 
WHERE datetime_col &gt; dateadd(hh, datediff(hh, 0, getdate()),0)
AND (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 &lt; 20',
</code></pre>
</div>

<div>
Do I need to create an actual backup first? 
 
If so, I create a backup with the script file. When I clicked ok, just the backup ran, that all..
</div>

<div>
&gt;&gt; Do I need to create an actual backup first? 
 
Nope, for more clarity, follow the steps below: 
1. Create a SQL file with the below script after replacing all necessary values.. 

<pre><code class="code-10 nolinks" id="code-20-42764151-1">EXEC msdb.dbo.sp_send_dbmail
@recipients=N'&lt;recipient_email_addresses&gt;',
@copy_recipients=N'&lt;copy_email_addresses&gt;',
@body='&lt;Message_Body&gt;',
@subject ='&lt;Message_Subject&gt;',
@query = 'SELECT AccountingRef, Description, LocalUnitPrice, LocalCost, (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 Margin FROM ur_Table 
WHERE datetime_col &gt; dateadd(hh, datediff(hh, 0, getdate()),0)
AND (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 &lt; 20',
@query_attachment_filename= 'File_List',
@attach_query_result_as_file = 1 ; 
</code></pre>
2. Now follow the steps from 
<blockquote>
Creating the Backup Task in the Task Scheduler
</blockquote>
 to the steps before 
<blockquote>
Restoring the Data
</blockquote>
 Section..
</div>

<div>
The Run Line doesn't look like that in Windows 7. I cant see in the newer version where the run line is to amend.
</div>

<div>
<div class="content wysiwyg-content">
Hello, I have the below SQL Trigger which is not 'firing'. SQL creates the trigger successfully. 
The trigger should only 'fire' when a transactions margin is less than 20 (20%). 
 
I think the issue is in this line: If (Select (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 from inserted) &lt;&nbsp;20 
 
Below is the code: 
 
USE ABC123 
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 @Description varchar(100) 
declare @body_msg varchar(1000) 
 
SELECT @AccountingRef = AccountingRef FROM INSERTED 
SELECT @Description = Description FROM INSERTED 
SELECT @body_msg = 'A product code with Tax Invoice Number &amp;&nbsp;Product Description: ' + @AccountingRef + @Description + 
'has been sold to a Customer where the margin is less than 20%. Please review this transaction to maintain your profit margins. 
You may need to issue a Credit Adjustment Note to correct the cost or sell price, then re-invoice.' 
 
If (Select (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 from inserted) &lt;&nbsp;20 
Begin 
 
Your help would be much appreciated.
</div>
</div>

Hello, I have the below SQL Trigger which is not 'firing'. SQL creates the trigger successfully.
The trigger should only 'fire' when a transactions margin is less than 20 (20%).

I think the issue is in this line: If (Select (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 from inserted) < 20

Below is the code:

USE ABC123
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 @Description varchar(100)
declare @body_msg varchar(1000)

SELECT @AccountingRef = AccountingRef FROM INSERTED
SELECT @Description = Description FROM INSERTED
SELECT @body_msg = 'A product code with Tax Invoice Number & Product Description: ' + @AccountingRef + @Description +
'has been sold to a Customer where the margin is less than 20%. Please review this transaction to maintain your profit margins. 
You may need to issue a Credit Adjustment Note to correct the cost or sell price, then re-invoice.'

If (Select (LocalUnitPrice-LocalCost) / LocalUnitPrice * 100 from inserted) < 20
Begin

Your help would be much appreciated.

SQL Trigger Not Working

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.