August 20, 2015 at 10:11 am
Its not sending me the email after it inserts. If I use update it sends me 2 emails. I only want it to send a email once the data is inserted.
USE [TEST]
GO
/****** Object: Trigger [dbo].[checkover2millionbeforpost] Script Date: 8/20/2015 10:53:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[checkover2millionbeforpost]
ON [dbo].[PM10000]
after insert
AS
begin
DECLARE @user-id varchar(20),
@getdate-2 varchar(39),
@body varchar(2000),
@DBNAMEvarchar(10),
@NewVENDORID varchar(20),
@Newbachnumb varchar(10),
@Newdocdate varchar(39),
@Newvendname varchar(50),
@Newdocamnt varchar(30),
@NewPtdusrid varchar(10)
SELECT @NewVENDORID = VENDORID
FROM Inserted
SELECT @NewBachnumb = BACHNUMB
FROM Inserted
SELECT @NewDocdate = convert(varchar,DOCDATE,107)
FROM Inserted
SELECT @Newdocamnt = DOCAMNT
FROM Inserted
SELECT @NewPtdusrid = PTDUSRID
FROM Inserted
SELECT @user-id = RTRIM(sp.loginame),
@getdate-2 = RTRIM(getdate())
FROM Master..sysprocesses sp
WHERE spid=@@spid
/*
select bachnumb, docdate, vendorid, DOCAMNT, ptdusrid from pm10000
where DOCAMNT >= 2000000.00000
*/
SET @body = @user-id+' ' + 'Has Entered a Payables Check Batch over 2 Million Dollars:' + ' ' + 'ON SERVER' +' ' + @@SERVERNAME + Char(13)+ Char(13)
+ 'Bachnumb' + ' ' + '=' + ' ' +rtrim(@NewBachnumb)+ Char(13)
+ 'Docamnt' + ' ' + '=' + ' ' +rtrim(@Newdocamnt)+ Char(13)-- + Char(13)
+ 'VendorID' + ' ' + '=' + ' ' + rtrim(@NewVENDORID) + Char(13)
+'______________________________________________________________' + Char(13)
+ 'On'+' ' + @Newdocdate
IF @Newdocamnt >= 2000000.00000
/*
IF @OldVADDCDPR <> @NewVADDCDPR or @OldAddress1 <> @NewAddress1 OR @OldAddress2 <> @NewAddress2
OR @OldAddress3 <> @NewAddress3 OR @OldCity <> @NewCity OR @OldState <> @NewState
OR @OldZipcode <> @NewZipcode OR @OldVNDCNTCT <> @NewVNDCNTCT
*/
--BEGIN
--SET NOCOUNT ON
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'lcarrethers@dolese.com',
--@copy_recipients = 'gbolding@dolese.com; angiew@dolese.com',
-- @blind_copy_recipients = @BLIND_COPY_RECIPIENTS,
@subject = 'Has Entered a Payables Check Batch over 2 Million Dollars',
@body = @body
END
August 20, 2015 at 10:23 am
lcarrethers (8/20/2015)
Its not sending me the email after it inserts. If I use update it sends me 2 emails. I only want it to send a email once the data is inserted.USE [TEST]
GO
/****** Object: Trigger [dbo].[checkover2millionbeforpost] Script Date: 8/20/2015 10:53:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[checkover2millionbeforpost]
ON [dbo].[PM10000]
after insert
AS
begin
DECLARE @user-id varchar(20),
@getdate-2 varchar(39),
@body varchar(2000),
@DBNAMEvarchar(10),
@NewVENDORID varchar(20),
@Newbachnumb varchar(10),
@Newdocdate varchar(39),
@Newvendname varchar(50),
@Newdocamnt varchar(30),
@NewPtdusrid varchar(10)
SELECT @NewVENDORID = VENDORID
FROM Inserted
SELECT @NewBachnumb = BACHNUMB
FROM Inserted
SELECT @NewDocdate = convert(varchar,DOCDATE,107)
FROM Inserted
SELECT @Newdocamnt = DOCAMNT
FROM Inserted
SELECT @NewPtdusrid = PTDUSRID
FROM Inserted
SELECT @user-id = RTRIM(sp.loginame),
@getdate-2 = RTRIM(getdate())
FROM Master..sysprocesses sp
WHERE spid=@@spid
/*
select bachnumb, docdate, vendorid, DOCAMNT, ptdusrid from pm10000
where DOCAMNT >= 2000000.00000
*/
SET @body = @user-id+' ' + 'Has Entered a Payables Check Batch over 2 Million Dollars:' + ' ' + 'ON SERVER' +' ' + @@SERVERNAME + Char(13)+ Char(13)
+ 'Bachnumb' + ' ' + '=' + ' ' +rtrim(@NewBachnumb)+ Char(13)
+ 'Docamnt' + ' ' + '=' + ' ' +rtrim(@Newdocamnt)+ Char(13)-- + Char(13)
+ 'VendorID' + ' ' + '=' + ' ' + rtrim(@NewVENDORID) + Char(13)
+'______________________________________________________________' + Char(13)
+ 'On'+' ' + @Newdocdate
IF @Newdocamnt >= 2000000.00000
/*
IF @OldVADDCDPR <> @NewVADDCDPR or @OldAddress1 <> @NewAddress1 OR @OldAddress2 <> @NewAddress2
OR @OldAddress3 <> @NewAddress3 OR @OldCity <> @NewCity OR @OldState <> @NewState
OR @OldZipcode <> @NewZipcode OR @OldVNDCNTCT <> @NewVNDCNTCT
*/
--BEGIN
--SET NOCOUNT ON
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'lcarrethers@dolese.com',
--@copy_recipients = 'gbolding@dolese.com; angiew@dolese.com',
-- @blind_copy_recipients = @BLIND_COPY_RECIPIENTS,
@subject = 'Has Entered a Payables Check Batch over 2 Million Dollars',
@body = @body
END
Your trigger has a MAJOR flaw. It is using scalar variables and assumes that there will only ever be one row in inserted. Also it is impossible that this trigger sends any email to anybody on an UPDATE, this is an INSERT trigger. Do you also have an UPDATE trigger?
This trigger needs to be rewritten to handle multiple row operations. I suspect your update trigger is much the same.
_______________________________________________________________
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/
August 20, 2015 at 10:27 am
If I change the word insert to update it works
August 20, 2015 at 10:46 am
lcarrethers (8/20/2015)
If I change the word insert to update it works
If you change the word INSERT to UPDATE it becomes an UPDATE trigger. You still have the logical flaw of assuming this runs for every row. In sql server triggers fire once per operation. If you update more than 1 row in a single update statement your trigger will only fire for a single row. This is a very common but extremely major mistake.
_______________________________________________________________
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply