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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply