Results 1 to 18 of 18

Thread: Email when record added to database

  1. #1

    Thread Starter
    Member
    Join Date
    Nov 2005
    Posts
    40

    Email when record added to database

    I currently created an application in asp where a user can enter a project request and I would like to know how to have an email sent to a specified person when a new recorded has been added. I have not ever used this before and not sure what I need to do for this. Wondering if I could get any feedback on how to do this and how complex this would be to complete.

    Thx

  2. #2
    Banned
    Join Date
    Nov 2005
    Posts
    2,367

    Re: Email when record added to database

    It's not too bad if you have MSSQL.
    MS SQL 2000 INSERT Trigger code:
    Code:
    CREATE TRIGGER trig_NewEmp ON [dbo].[Employees] 
    FOR INSERT
    AS
    	EXEC Master .. XP_SENDMAIL @recipients = 'Me@MyDomain', @subject = 'New Emp Added', @Message = 'We got new peeps'
    That snippet is designed for NorthWind.Employees

  3. #3

    Thread Starter
    Member
    Join Date
    Nov 2005
    Posts
    40

    Re: Email when record added to database

    That does look very simple but can you explain this code to me somewhat. I understand triggers but what is the FOR INSERT for?

    thx

  4. #4
    Banned
    Join Date
    Nov 2005
    Posts
    2,367

    Re: Email when record added to database

    You can apply a trigger to happen for INSERT, UPDATE and/or DELETE. That line is saying "On Insert, do this."

  5. #5

    Thread Starter
    Member
    Join Date
    Nov 2005
    Posts
    40

    Re: Email when record added to database

    Als would this go in the page with the submit button or the save page?

  6. #6
    Banned
    Join Date
    Nov 2005
    Posts
    2,367

    Re: Email when record added to database

    Neither, it goes on your database.

    The code is transact. If you're using MS SQL, right click the table and go to design. One of the toolbar buttons will be called "Triggers." If you click on that, the template is loaded and you can apply code there.

    If you're wanting to execute this everytime a record is added to the table, it's best to do it in the database. A year or two from now, there could potentially be other applications that will insert into this table as well. If you have the functionality tied to the table and not your application, it will work without a hitch.

  7. #7
    Banned
    Join Date
    Nov 2005
    Posts
    2,367

    Re: Email when record added to database

    If you want to go the app-side route, this thread might be of some help:
    http://www.vbforums.com/showthread.php?t=388700

  8. #8

    Thread Starter
    Member
    Join Date
    Nov 2005
    Posts
    40

    Re: Email when record added to database

    OK I understand some what now but I get an error when I try to add a new record:

    Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
    [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'xp_sendmail', database 'master', owner 'dbo'.

    Do I need to add something to my insert statement?
    Last edited by ldy_hale; Apr 28th, 2006 at 11:40 AM. Reason: need to ask something else

  9. #9
    Banned
    Join Date
    Nov 2005
    Posts
    2,367

    Re: Email when record added to database

    You need to give dbo (or whatever login/user you plan on using) permission to xp_sendmail.

    To do this (I'll use dbo as an example), you would do the following:
    Go into your master database, right click the user in question and select properties. From here, click the permissions buttons and you should get something like the following on your screen:

    Make sure that xp_sendmail has execute priviledges for that user.

  10. #10
    Banned
    Join Date
    Nov 2005
    Posts
    2,367

    Re: Email when record added to database

    Do I need to add something to my insert statement?
    Nope, nothing. This is totally database side. Anytime you use a program, QA or manually insert a record; this trigger will fire.

  11. #11

    Thread Starter
    Member
    Join Date
    Nov 2005
    Posts
    40

    Re: Email when record added to database

    OK, I understand. I do not have access to go in and set that setting, since it is for my company. I guess I will have fill out more paper work to get that done. I do appreciate all your help. You are very knowledgable about ASP.

    Thx

  12. #12
    Fanatic Member
    Join Date
    Nov 2003
    Posts
    683

    Re: Email when record added to database

    I am interested in using this trigger method to send emails. I am looking at your screenshot of the database user but when I bring up my database user there are no xp_anythings. Is there something I need to do to add them first? I wish to use the xp_sendemail.

  13. #13
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174

    Re: Email when record added to database

    What are you logged in as? sa?

  14. #14
    Fanatic Member
    Join Date
    Nov 2003
    Posts
    683

    Re: Email when record added to database

    The administrator for the box, not the sa.

  15. #15
    Banned
    Join Date
    Nov 2005
    Posts
    2,367

    Re: Email when record added to database

    Are you looking through the Master database permissions ?

    The function is specific to the master database, it won't be listed under anywhere else. You'd need to alter it there.

  16. #16
    Fanatic Member
    Join Date
    Nov 2003
    Posts
    683

    Re: Email when record added to database

    Thank you. that was it.

  17. #17
    Fanatic Member
    Join Date
    Nov 2003
    Posts
    683

    Re: Email when record added to database

    I have the trigger setup but it doesn't seem to do anything so I put the trigger in to the sql query analyzer and got this for an error:

    Server: Msg 18030, Level 16, State 1, Line 0
    xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

    My query is
    Exec Master .. XP_SENDMAIL @recipients = Beast777@force.gc.ca', @subject = 'New Telecomm Request'

  18. #18
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174

    Re: Email when record added to database


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width