>sp_send_dbMail - the SP Hugo is using. In any case, the call to this SP must be in a job, not in a trigger - that was my point.

I'll try to answer everyone from this message, hope I can be clear for once in my life (not brief for sure)

First of all I did not explain very well what I was trying to accomplish, what a surprise! I will try this time trying to follow my line of thinking, if I am indeed capable of such thing.

Craig is concerned about Business Rules and Separation of Concerns but the problem is that my table is the business rule, if you allow me to call it like that. Why? Because the
sole purpose of it's existence is to facilitate other (multiple) un-attended applications to send e-mail notifications. You might be wondering why not simply send the e-mail; and the answer is pretty simple, not every computer is authorized to relay, which means that Exchange will refuse to send SMTP requests from any but a couple of computers in the organization. So, following the old concept we were using with our foxpro application, the natural replacement would be to write a similar application (we could also just upgrade this old application to meet the new requests, but we will ignore this alternative in this thread) but as a .NET Service with a SQL Server back-end, and in fact this is were I started.

Now, lets think again... why don't we just call the sp_send_dbmail stored procedure from our applications and be done with it? We just pass the parameters and voila we accomplished
the task. This has a minor issue thou, as we do want to have a log of this request, and although for this we could use the standard mail log of SQL Server, we also want to add more
information and then we will need to create our own log table to store this, so it is clear that using the sp_send_dbmail should be called from either a custom stored procedure or from some custom application functions (yes I used plural here to show a big dis-advantage of the function approach, which is by following this approach we will need at least 2, one for any foxpro related application and another for any .NET related application)

Now, we are in a point were we need to either write a custom store procedure to call sp_send_dbmail and log our custom information into our log table or write those functions in
different languages, which for me it is clearly not a good idea, so we write the store procedure which basically calls sp_send_dbmail and the adds the extra information into the
log table and this seems fine to me but then, is this stored procedure really needed? What if instead we just have the log table, and when a record is inserted into it we fire an
instead of trigger that will, in the end, do the same thing with the advantage (for me, I understand that for others calling the stored procedure directly would be better, but for
me it is easier to use INSERT) that from your application is just a simple INSERT INTO command, it also has the advantage of easier security setup (only one object to secure) and the dis-advantage of doing something that might not be noticed if you do not know the purpose of the table. But it also has the advantage of ensuring that each record in the log has been sent to sp_send_dbmail, which is not true if you are using a stored procedure, as someone with enough rights could directly add a record to the table without using the stored procedure, which complicates the security settings.

Now, I understand that outside the context of what I want to accomplish, having an instead of trigger to send e-mails can be a very bad idea, but it is not my intention to do so in any table (I mean as a rule, this would be the exception), in fact my plan to doit like this is because, as I said at the beginning, is the only purpose of the table's existence. There is an important issue I want to address as a disadvantage of having either a service or a SQL Server Agent watching a log table, as what happens is that it is possible for the service/application to be stopped for any reason, either a bug or someone intentionally stopping it, which then requires to have extra logic to "time-out" messages, for an e-mail that is sent one hour after a train passed by a track section to notify the position of a train is of hardly any use and it will confuse more than help if send, and although the logic is not complex, you probably want to have some configuration table for the time-out value for different applications, for example in the aforementioned case it will probably be less than a minute, but reminding a user of a forgotten password does not need to time out, so this will need extra administrative work.


In brief, I think the INSTEAD OF TRIGGER is the cleanest and more elegant (but I understand that this is only my POV) solution to the problem as it solves all the issues in one simple trigger, the main disadvantage is that the casual observer might miss the purpose of the table but that is something I can live with.

Hope this long and boring message clarified things a little.