September 13, 2011 at 4:34 am
Hi all
I'm creating a trigger that should send a mail if a stockcode is created in the inventory table. A mail is only sent if the the stockcode starts with a Y.
I have...
USE EncoreCompanyT;
GO
/* Delete the trigger */
IF OBJECT_ID ('dbo.trg_new_stockcode_dispensary', 'TR') IS NOT NULL
DROP TRIGGER dbo.trg_new_stockcode_dispensary;
GO
CREATE TRIGGER dbo.trg_new_stockcode_dispensary
ON dbo.InvMaster
AFTER INSERT
AS
IF (SELECT count(StockCode) FROM inserted i WHERE StockCode LIKE 'Y%') >= 1
SET NOCOUNT ON
BEGIN
--Declare variables and set them to values from the inserted table
DECLARE @distnvarchar(max);-- email distribution list
DECLARE @SCchar(30);-- StockCode
DECLARE @DESchar(30);-- Description
SELECT @SC= i.StockCode FROM inserted i;
SELECT @DES= i.Description FROM inserted i;
SET @dist = 'myemail@mycompany.co.uk'
BEGIN
DECLARE @sHTMLnvarchar(MAX);
DECLARE @sSubjectnvarchar(55);
SET @sHTML = N'<font face = "Arial">Please find the details for a newly added stockcode....</font>' +
N'<dl><dt>-</dt></dl>' +
N'<dl><font face = "Arial">' +
N'<dt>Stockcode : ' + @SC +
N'</dt><dt>Description : ' + @DES +
N'</dt></font></dl>' +
N'</>' +
N'</><font face = "Arial">' +
N'<h3>Please update the Nexus details within the Dispensary Manager</h1></font>' +
N'</>' +
N'<h5>Version 1 (13.09.2011)</h5>';
SET @sSubject = 'New Chemical Stockcode : ' + Cast(getdate() as varchar)
EXECmsdb.dbo.sp_send_dbmail
@profile_name = 'sqlServer',
@recipients = @dist,
@subject = @sSubject,
@body = @sHTML,
@body_format = 'HTML';
END
The IF statement is where i thought the stockcode validation from the inserted table should be but i'm getting a mail for all codes created. Can anyone see what i've got wrong??
Thanks,
Spin.
September 13, 2011 at 4:44 am
ok, think i've sussed it.
I switched the section..
IF (SELECT count(StockCode) FROM inserted i WHERE StockCode LIKE 'Y%') >= 1
SET NOCOUNT ON
BEGIN
...to be....
IF (SELECT count(StockCode) FROM inserted i WHERE StockCode LIKE 'Y%') >= 1
BEGIN
SET NOCOUNT ON
...and it now seems to work.
seems strange but it works.
Thanks (to me).
September 13, 2011 at 5:34 am
Not so strange, really.
Your original code was doing SET NOCOUNT ON if the condition was true and then reverting to normal execution.
Unless your if-statement code is of the form
if [condition]
begin
multiple lines of code here
end
only the statement which comes immediately after the IF will be considered as part of the IF construct.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy