Email Trigger If Statement

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

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

  • 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