database mail trigger on insert

Question

Post reply

database mail trigger on insert

spin

Hall of Fame

Points: 3274
More actions
March 9, 2011 at 9:29 am

#157480

Hi,
i am trying to create a trigger to send an automated mail after an insert to my inventory table that attaches a file with the inserted record only.
This is what i have so far....
truncate tableEncoreCompanyT.dbo.InvNewStockode
insert intoEncoreCompanyT.dbo.InvNewStockode
select StockCode, Description, LongDesc, Supplier, Buyer
from INSERTED
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'gfrewin@dddltd.co.uk',
@body = 'Please find the details for a newly added stockcode.',
@query ='select * From EncoreCompanyT.dbo.InvNewStockode',
@subject = 'New Stockcode',
@attach_query_result_as_file = 1,
@query_result_separator = '',
@query_attachment_filename = 'New_Stockcode.csv'
..but when i try to insert from the application (Syspro) i get an error??
can anyone help?
Thanks.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

What does the error message say?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

spin Hall of Fame Points: 3274 More actions · Answer 2

--truncate table EncoreCompanyT.dbo.InvNewStockode

--insert into EncoreCompanyT.dbo.InvNewStockode

--select StockCode, Description, LongDesc, Supplier, Buyer

--from INSERTED

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'gfrewin@dddltd.co.uk',

@body = 'Please find the details for a newly added stockcode.',

@query ='select * From EncoreCompanyT.dbo.InvNewStockode',

@subject = 'New Stockcode',

@attach_query_result_as_file = 1,

@query_result_separator = '',

@query_attachment_filename = 'New_Stockcode.csv'

Hi,

if i take out the commented part the trigger works fine but as soon as i put it back in i get and error within syspro.

when i run just the part ...

truncate table EncoreCompanyT.dbo.InvNewStockode

insert into EncoreCompanyT.dbo.InvNewStockode

select StockCode, Description, LongDesc, Supplier, Buyer

from INSERTED

...OR (without the INSERTED call)...

truncate table EncoreCompanyT.dbo.InvNewStockode

insert into EncoreCompanyT.dbo.InvNewStockode

select StockCode, Description, LongDesc, Supplier, Buyer

from EncoreCompanyT.dbo.InvMaster

i get the error message from Syspro :

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "InvNewStockode" because it does not exist or you do not have permissions.

...I know i have permission because i've created the table and if i run the above from within SQL Server (2005) it works.

is there a timing issue or permissions from Syspro issue maybe??

Thanks.

Kiara SSCarpal Tunnel Points: 4185 More actions · Answer 3

Can you confirm that this is what's happening?

You wrote and tested your trigger on the database table in Management Studio, probably using sa or equivalent rights.

When someone inserts into the table the trigger is on from within the application, an error that states that the user doesn't have permissions to insert... pops up.

If that is the case, then I'd check to see what user or role the application is actually using. That user will also need permissions to insert into the table your trigger is trying to insert into.

-Ki

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Kiara is on to something with permissions. Also make sure the user executing the statement that is firing the trigger has permissions to TRUNCATE the table.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

GSquared SSC Guru Points: 260824 More actions · Answer 5

You need Alter Object permissions to truncate, because of what it does to Identity columns and all that.

You might be better off skipping that step entirely. You can include query results (from inserted/deleted or from a real table) in DBMail more easily by using the XML trick that Microsoft illustrates in their sample code for sp_Send_DBMail. Check out the samples on MSDN for that proc, and you'll find one that has a neat trick for turning query results into an HTML table. Much better, in my opinion, than using the query parameter. Looks better and works more easily.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sossoliso SSC Enthusiast Points: 196 More actions · Answer 6

Bad Idea..

So not send mail from a trigger.

Get the Trigger to write Whatever you want into some table.

Create a SQL Server Job to poll the said table every couple of minutes and send the mail.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Lionel E. Nzenze (3/11/2011)
Bad Idea..
So not send mail from a trigger.
Get the Trigger to write Whatever you want into some table.
Create a SQL Server Job to poll the said table every couple of minutes and send the mail.

Database Mail runs on top of Service Broker so the "holding tank" architecture is already there under the covers...i.e. the sending of the email does not complete within the transaction of the trigger, only the addition of the mail details to the Service Broker queue happens in the transaction and that is very fast and reliable.

If the goal is to be able to review emails before they are sent you can disable the Service Broker Mail Queue which allows Database Mail to queue the messages but prevents Service Broker from sending them. When you're ready to send them you can simply activate the Mail Queue.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

GSquared SSC Guru Points: 260824 More actions · Answer 8

Lionel E. Nzenze (3/11/2011)
Bad Idea..
So not send mail from a trigger.
Get the Trigger to write Whatever you want into some table.
Create a SQL Server Job to poll the said table every couple of minutes and send the mail.

Actually, that's pretty much exactly what sp_Send_DBMail already does. It's an asynchronous process, which queues up the mail in msdb, and then sends it separately.

There isn't really a good reason that I know of to build your own queue. Used to be, in SQL 2000, where it pretty much had to use Outlook on the server to send stuff, but not so much any more since SQL 2005.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

spin Hall of Fame Points: 3274 More actions · Answer 9

hi,

just got my head around all of the responses.

so...It turns out i was running trigger to email on our test system which doesn't have the correct permissions to truncate the tables. I've now got the script working on the live system.

I wanted to ask Sossoliso why it's not a good idea to send an email direct from a trigger?

thanks all. spin 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 10

spin (3/14/2011)
hi,
just got my head around all of the responses.
so...It turns out i was running trigger to email on our test system which doesn't have the correct permissions to truncate the tables. I've now got the script working on the live system.
I wanted to ask Sossoliso why it's not a good idea to send an email direct from a trigger?
thanks all. spin 🙂

See Grant's response; basically, in SQL2000, when mail was syncronous, it was a bad idea to send mail in a trigger; it's something that was addressed in 2005+, so it's out of date advice.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sossoliso SSC Enthusiast Points: 196 More actions · Answer 11

I once Supported an Finance/Accounts System which used Triggers to alert agents when a sale had been made. One day the trigger to could send out any mails... The system became unsuable because data was roilled back each time a sale was made.

if anything happened between the data changes and sending of mail.. what happens next?

Either the data change gets rolled back or process continues in which case the sending oif the mail procedure gets lost.

Sending of mail may not always be as quick as would be liked.. so trigger creates bottleneck in the process.. There may be blogs out there about this issue.. cannot find any at the moment.. speaking from experience..

happy mailing.

spin Hall of Fame Points: 3274 More actions · Answer 12

great, thanks.

I see your point and def don't want to start rolling back transactions. Am i right in thinking that i don't need to worry about this in 2005 as the emailing will not rollback the insert??

Finally on this thread i am trying to add the current date to the subject of my email. so, something like

@subject = 'New Stockcode : ' + getdate()

...but it doesn;t like this. i get :Incorrect syntax near '+'

anyone know what i should be doing?

thanks.

Sossoliso SSC Enthusiast Points: 196 More actions · Answer 13

Declare @val varchar(250)

Set @val = 'New Stockcode : ' + Cast(getdate() as varchar)

@Subject = @val

krushkoder Ten Centuries Points: 1149 More actions · Answer 14

@subject = 'New Stockcode : ' + convert(varchar(20),getdate())

It's trying to add 'New Stockcode : ' to the date... If you convert it to a string, it will work.

KK