E-Mail Triggers

  • My first post here, and a newbie in SQL

    I have written a few triggers before, This one does not seems to work. Is it my nested select? I'm not getting any errors. Just nothing is e-mailed.

    Use ABCTRAINING

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    alter TRIGGER TAXPERCENT

    ON [dbo].[AP101_VOUCHER_DISTR] AFTER update AS

    declare @vat numeric(15,2),

    @Total numeric(15,2),

    @VATPERCENT numeric(15,2),

    @Voucher nvarchar(max),

    @AMT numeric(15,2),

    @GL numeric(15,2)

    Select @Voucher = i.ap101_voucher,

    @AMT = i.ap101_amt,

    @GL = i.AP101_GL_ACCT,

    @vat = (select sum(iif(@GL = '70102501110' or @GL = '70102501120',@AMT,0))),

    @Total = (select sum(@AMT)),

    @VATPERCENT = @vat/(@Total-@VAT)

    from inserted i

    if @VATPERCENT <> 0.140

    BEGIN

    DECLARE @msg nvarchar(MAX)

    Declare @Subj varchar(MAX)

    Declare @em nvarchar(MAX)

    SET @msg = 'The VAT on this Voucher ' + @Voucher + ' ' + 'does not equal 14% ' + ' ' + @VATPERCENT

    SET @Subj = 'Voucher Error'

    set @em = 'xxxxxx@company.co.za'

    EXEC msdb.dbo.sp_send_dbmail

    @importance ='High',

    @recipients=@EM,

    @body= @msg,

    @subject = @Subj,

    @body_format = 'HTML',

    @profile_name = 'ABC Profile'

    END

    Any help will be appreciated

    Thanks

  • i think problem is in calculation in select statement.

    try in this way

    Select @Voucher = i.ap101_voucher,

    i.ap101_amt,

    i.AP101_GL_ACCT into #temp

    from inserted i

    select @VATPERCENT=( sum(case when AP101_GL_ACCT in ('70102501110', '70102501120') then ap101_amt else 0 end ))/

    (sum(ap101_amt) - sum(case when AP101_GL_ACCT in ('70102501110', '70102501120') then ap101_amt else 0 end ) )

    from #temp

    if @VATPERCENT <> 0.140

    BEGIN

    DECLARE @msg nvarchar(MAX)

    Declare @Subj varchar(MAX)

    Declare @em nvarchar(MAX)

    SET @msg = 'The VAT on this Voucher ' + @Voucher + ' ' + 'does not equal 14% ' + ' ' + @VATPERCENT

    SET @Subj = 'Voucher Error'

    set @em = 'xxxxxx@company.co.za'

    EXEC msdb.dbo.sp_send_dbmail

    @importance ='High',

    @recipients=@EM,

    @body= @msg,

    @subject = @Subj,

    @body_format = 'HTML',

    @profile_name = 'ABC Profile'

    END

    value of @VATPERCENT is null, try to send its value on email. remove if statement

  • From the first glace you have couple of issue here:

    1. The trigger is not designed for multiple records meaning the INSERTED (and DELETED) table can return more than one row.

    2. All values in one select statement are executed at once (not in column order). Therefore you cannot reference to a variable that you just set in the same query.

    Meaning in

    @VAT = (select sum(iif(@GL = '70102501110' or @GL = '70102501120',@AMT,0)))

    @GL and @AMT will be NULL.

    3. Any calculation/operation with NULL will return NULL in the standard setup.

    Therefore

    @VATPERCENT = @vat/(@Total-@VAT)

    will return NULL.

    4. Any comparison with NULL will return false (in the standard setup)

    Therefore the

    if @VATPERCENT <> 0.140"

    will always be false and never send an email.

    Hope that helps.

    Regards,

    Alex

  • Hi,

    Thanks for that. I changed it to this as vouchers contain many lines. The following works perfectly in man studio. I added it to my trigger, but nothing still happens, even if I comment the "if statement" out

    Select ap101_voucher,

    (select sum(case when AP101_GL_ACCT in ('70102501110', '70102501120') then ap101_amt else 0 end )) as Tax,

    (sum(AP101_AMT)) as Total

    into #temp

    from inserted

    group by AP101_VOUCHER

    select ap101_voucher,

    Total/(Total-Tax) as VATPERCENT

    from #temp

    How do I put this into the trigger successfully?

  • Can you post the entire contents of your trigger?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Tis should at least send me a mail regardless of if statement.

    Use ABCTRAINING

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    alter TRIGGER TAXPERCENT

    ON [dbo].[AP101_VOUCHER_DISTR] AFTER insert AS

    declare @vat numeric(15,2),

    @Total numeric(15,2),

    @VATPERCENT numeric(15,2),

    @Voucher nvarchar(max),

    @AMT numeric(15,2),

    @GL numeric(15,2)

    Select ap101_voucher,

    (select sum(case when AP101_GL_ACCT in ('70102501110', '70102501120') then ap101_amt else 0 end )) as Tax,

    (sum(AP101_AMT)) as Total

    into #temp

    from inserted

    group by AP101_VOUCHER

    select ap101_voucher,

    Total/(Total-Tax) as VATPERCENT

    from #temp

    --if @VATPERCENT <> 0.140

    BEGIN

    DECLARE @msg nvarchar(MAX)

    Declare @Subj varchar(MAX)

    Declare @em nvarchar(MAX)

    SET @msg = 'The VAT on this Voucher ' + @Voucher + ' ' + 'does not equal 14% ' + ' ' + VATPERCENT

    SET @Subj = 'Voucher Error'

    set @em = 'xxxxxx@company.co.za'

    EXEC msdb.dbo.sp_send_dbmail

    @importance ='High',

    @recipients=@EM,

    @body= @msg,

    @subject = @Subj,

    @body_format = 'HTML',

    @profile_name = 'ABC Profile'

    END

  • pieter 46001 (9/9/2013)


    Tis should at least send me a mail regardless of if statement.

    Use ABCTRAINING

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    alter TRIGGER TAXPERCENT

    ON [dbo].[AP101_VOUCHER_DISTR] AFTER insert AS

    declare @vat numeric(15,2),

    @Total numeric(15,2),

    @VATPERCENT numeric(15,2),

    @Voucher nvarchar(max),

    @AMT numeric(15,2),

    @GL numeric(15,2)

    Select ap101_voucher,

    (select sum(case when AP101_GL_ACCT in ('70102501110', '70102501120') then ap101_amt else 0 end )) as Tax,

    (sum(AP101_AMT)) as Total

    into #temp

    from inserted

    group by AP101_VOUCHER

    select ap101_voucher,

    Total/(Total-Tax) as VATPERCENT

    from #temp

    --if @VATPERCENT <> 0.140

    BEGIN

    DECLARE @msg nvarchar(MAX)

    Declare @Subj varchar(MAX)

    Declare @em nvarchar(MAX)

    SET @msg = 'The VAT on this Voucher ' + @Voucher + ' ' + 'does not equal 14% ' + ' ' + VATPERCENT

    SET @Subj = 'Voucher Error'

    set @em = 'xxxxxx@company.co.za'

    EXEC msdb.dbo.sp_send_dbmail

    @importance ='High',

    @recipients=@EM,

    @body= @msg,

    @subject = @Subj,

    @body_format = 'HTML',

    @profile_name = 'ABC Profile'

    END

    This trigger has some major flaws. It still seems like you are really doing this as if there were ever only be 1 row inserted. You have managed to change up a couple things but if you insert more than 1 voucher it is still only going to send 1 email.

    Take a look at the line where you assign @msg. The value will always be NULL because @Voucher is never assigned a value. Also, you then append VATPERCENT instead of a variable. To be honest, I am surprised this would compile because VATPERCENT is not a variable.

    The two select statements don't do anything other than add results to your insert statement. I am not at all sure why you are using a temp table here.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Let me ask you one thing: if the call to dbo.sp_send_dbmail, or for that matter anything else in the trigger, fails, do you want the statement that fired the trigger to roll back?

    This is a critical question, because if the answer is no, this code should not be in a trigger at all. A trigger is logically part of the statement that fired it.

    Maybe you should have a job which tracks change rows (this can be implemented with Change Tracking) and send mails as needed?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply