Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
m245
Starting Member
2 Posts |
 Posted - 2010-11-12 : 23:11:03
|
| I want the system to send me an email each time a table is updated so I write this simple trigger to do that create trigger dbo.sendmail_customer on CUSTOMER for update As Begin exec master..xp_cmdshell 'c:\vbscript\sendmail.exe sendmail1.ini' EndIt works fine and an email is sent each time a record is updated. The problem is that each time when a user saves a record, the user interface (of an application which we have no access to its source code) shows something like "the record can't be edited because someone has changed it" even though the record has been updated successfully. I think it has something to do with the application checking the timestamp of the record but I don't know why adding a trigger will cause this problem. Any idea how to get around it?Many Thanks! |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-13 : 05:22:15
|
| I don't think your trigger will cause the error you are seeing. Most likely the error is being generated in response to a scenario and your change has caused that error message to be raised (even though its the wrong message for the actual situation) - sorry, not describing that very well at all.If you remove your Trigger does the problem go away? If not then the problem is elsewhere.I think the more likely scenario is that your trigger is causing a slow-down and that is causing a timeout in the APP, and the APP is (wrongly) displaying that particular message.Personally I would never put an external process like Send Email in a trigger. Way too slow, Way too fragile.And what if someone updates every row in the table for maintenance (in a loop)? Do you want thousands (millions?) of emails?For that sort of scenario we INSERT a row in an "EmailsToBeSent" table, and another process which runs every minute sends them out. The trigger is very fast (just "insert a row in a table"), and the separate Email routine can take as long as it likes ...Or we write to a log table and an email is generated once every 10 minutes if there are appropriate log messages - so at worst I get an email every 10 minutes, rather than one-per-changed-record |
 |
|
|
m245
Starting Member
2 Posts |
Posted - 2010-11-14 : 23:08:07
|
| Thanks for your reply, Kristen.The error was not there before I added the trigger. It's strange that if I replace the "exec master ...." statement with an "insert into" statement, the error goes away. I'm quite sure the error message is raised inside the application, which is a blackbox to me. Your guess about the trigger causing a slow-down is probably right and the suggestion of logging the changes and then use other process to check the log is a good one but before doing so I would like to see if there is a simple work around as I want to keep the solution simple. Also, the table will only be updated thru the application, so I'm not worried about bulk update. I will appreciate it if there are other suggestions. Thanks again. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-15 : 04:36:36
|
| "It's strange that if I replace the "exec master ...." statement with an "insert into" statement, the error goes away"I don't find that strange. The external SENDMAIL application is both slow, and fragile (i.e. may return error)A trigger needs to be extremely reliable and fast. If it is slow it will cause side effects such as records being locked / blocked, timeouts and other problems.Hence I would use the trigger to store a "job" in another table, and then have a scheduled task actually perform that job. In your case store "Email needs sending" in another table and have a scheduled job running every minute that does the SENDMAIL task. That means that the trigger is not held up."the table will only be updated thru the application, so I'm not worried about bulk update"In that case put a test in the Trigger that there is only one record being updated (and raise error otherwise) - otherwise, sooner or later, someone will do an adhoc update and the trigger will cause unexpected / unintended side effects |
|
|
|
|
|
|
|
|
Trigger