Post reply

database mail trigger on insert

  • 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 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'

    ..but when i try to insert from the application (Syspro) i get an error??

    can anyone help?

    Thanks.

  • March 9, 2011 at 10:41 am

    #1296653

    What does the error message say?

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

  • March 10, 2011 at 5:52 am

    #1297021

    --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.

  • March 10, 2011 at 5:58 am

    #1297025

    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

  • March 10, 2011 at 7:10 am

    #1297078

    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

  • March 10, 2011 at 7:14 am

    #1297081

    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

  • March 11, 2011 at 9:05 am

    #1297710

    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.

  • March 11, 2011 at 9:20 am

    #1297721

    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

  • March 11, 2011 at 9:21 am

    #1297722

    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

  • March 14, 2011 at 8:17 am

    #1298343

    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 🙂

  • March 14, 2011 at 8:21 am

    #1298346

    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!

  • March 14, 2011 at 8:57 am

    #1298376

    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.

  • March 14, 2011 at 9:07 am

    #1298387

    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.

  • March 14, 2011 at 9:14 am

    #1298390

    Declare @val varchar(250)

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

    @Subject = @val

  • March 14, 2011 at 9:15 am

    #1298392

    @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

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

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