April 24, 2017 at 1:31 pm
Have a table that when records are inserted will fire off email via trigger. One record works fine, if I do more than 1 it only sometimes work, I thought below should work but must be missing the obvious
CREATE TRIGGER tr_DWH_HRxsins
ON DWH_HRxsDEL
for INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @rxno varchar(12)
declare @hrxident varchar(12)
Select @rxno = inserted.rxno, @hrxident = inserted.hrxident from inserted
declare @record varchar(100)
select @record = 'Record RXNO ' + @rxno + ' HRXIDENT ' + @hrxident + ' has been deleted from hrxs table '
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'cmsdwsqldevsqlexec',
@recipients = 'name@corizonhealth.com',
@body = @record,
@subject = 'HRXS Record Deleted'
end
END
GO
April 24, 2017 at 2:31 pm
That trigger looks right to me.
When you say that it fires for the first insert but not others, how many rows are being inserted at once? What does the INSERT query look like?
If it is inserting multiple rows at once, then it will fire once.
If it is inserting 1 row at a time but has multiple inserts in a short period of time, it could be your mail server is seeing it as a DOS attack and is blocking some of the messages.
Since it works for 1 record, I am expecting that you are inserting multiple records at once (something like:INSERT INTO DWH_HRxsDEL
SELECT *
FROM DWH_HRxs
Is that the way you are inserting data into DWH_HRxsDEL or similar? If so, you will likely want to count the number of rows in inserted and loop through that many times to get the whole list of stuff that was deleted from hrsx table.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 24, 2017 at 2:34 pm
my bad the trigger will not work for multiple record deletes
April 24, 2017 at 2:39 pm
tcronin 95651 - Monday, April 24, 2017 2:34 PMmy bad the trigger will not work for multiple record deletes
You could make it work though using either a while loop or a cursor.
Or if you didn't want to do that, you could cast it to XML and send it that way, no?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 24, 2017 at 2:47 pm
Sue
April 24, 2017 at 3:20 pm
CREATE TRIGGER tr_DWH_HRxsins
ON dbo.DWH_HRxsDEL
AFTER INSERT
AS
SET NOCOUNT ON;
DECLARE @record varchar(1000)
SELECT @record = STUFF((
SELECT '. Record RXNO ' + CAST(i.id AS varchar(12)) + ' HRXIDENT ' + CAST(i.id AS varchar(12)) + ' has been deleted from hrxs table'
FROM dbs i
FOR XML PATH('')), 1, 2, '')
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'cmsdwsqldevsqlexec',
@recipients = 'name@corizonhealth.com',
@body = @record,
@subject = 'HRXS Record(s) Deleted'
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply