September 6, 2013 at 3:57 am
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
September 6, 2013 at 6:31 am
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
September 6, 2013 at 7:10 am
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
September 9, 2013 at 3:10 am
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?
September 9, 2013 at 8:20 am
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/
September 9, 2013 at 9:01 am
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
September 9, 2013 at 9:20 am
pieter 46001 (9/9/2013)
Tis should at least send me a mail regardless of if statement.
Use ABCTRAININGSET 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/
September 9, 2013 at 3:55 pm
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