Post reply

Can I create Email Trigger that compares column value in inserted row with value in another table?

  • August 21, 2011 at 1:09 am

    #243545

    I want to send out an email to Users if they enter incorrect data into database table. The User will record hours of work done for a client company. Whether my company charges for this work depends on whether the work was prior to an appointment date or after it. If it is prior they should enter a PRE file type and if after a POST file type. The table the work is recorded in is called the TAT_Time table. However, the table that the appointment date is in is the HBM_matter table. Is writing a trigger on an INSERT the best way to notify Users and is it possible to compare data entered with data in another table?

    Cheers,

    AndySugs 😀

  • August 22, 2011 at 4:31 am

    This was removed by the editor as SPAM

  • August 22, 2011 at 4:36 am

    #1371329

    andrew.sugden (8/21/2011)

    ...Is writing a trigger on an INSERT the best way to notify Users and is it possible to compare data entered with data in another table?

    ...

    Client-side data entry validation. The best way to notify users would be through the app, checking details at an appropriate moment. Yes, it's possible to compare data entered with data in another table - at more than one stage of the data entry process. How much control do you have over the client app?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 22, 2011 at 6:13 am

    #1371360

    I'm not sure client-side validation is a good fit for this problem as it needs comparison with other data that's in the database. Even if you went to the hassle of bringing the required data back client-side, what would happen if the data changed while the user was still editing?

    The database is the correct layer to do this check by the sounds of it, but probably not by a trigger and definitely not by accepting the data, then emailing back out from the database. The front end should submit the data to a stored procedure, which should check the data meets the rules, then either accept the data or raise an error, which is then returned back to the client application.

    You could even enforce multi-table business rules using indexed views if you wanted - worth taking a look here:

    http://spaghettidba.wordpress.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/

Viewing 4 posts - 1 through 3 (of 3 total)

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