insert query and email

Question

insert query and email

tcronin 95651

SSCertifiable

Points: 6735
More actions
April 24, 2017 at 1:31 pm

#337487

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

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.

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 2

my bad the trigger will not work for multiple record deletes

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

tcronin 95651 - Monday, April 24, 2017 2:34 PM
my 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.

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Triggers fire once per statement, not once per row which would explain the issue with it working for one record. In terms of the intermittent issue, have you check the database mail logs and tables? I could see where the body could end up null but you still have a subject so the mail should send - check the sysmail_allitems, sysmail_event_log tables in msdb.

Sue

ScottPletcher SSC Guru Points: 101105 More actions · Answer 5

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".