Mail when value of field < or > paramater%

  • Brussel

    SSC Rookie

    Points: 34

    Hello,

    I have a question and IĀ  don't know if it's possible to configure in SQL.

    You have a ERP GUI screen, with SQL tables, and the user changed a value of a field.

    In this printscreen the cost price. Then when the new value is < or > then 30% of the 'old' value a simple mail must be sent to a user. Let say ict@exchange.com (we use Exchange as mail server)

    So an example : TO user XXXX, Subject text, body text is enough.

    Or when the calculation of some fields < or > is then another field.

    Example : cost price + transport + packing cost - discount is < or > 40% off the sales price.

    As an example :

    Table : Articles

    Field : costprice, sales price, packing, transport and discount

    For me it's importent how to start. How to configure stuff like this.

    And then I can find/search on the internet for more solutions.

    But now I have no clue how to start / configure ?

    I'm a very beginner in SQL.

    erp gui

  • Thom A

    SSC Guru

    Points: 98219

    Wouldn't this logic be easier to implement into the application, rather than SQL Server? Otherwise you're going to need to (likely) rely on triggers, which shouldn't be used to send emails. You'll therefore need to store the details of the change in a table and then (regularly) query that data using agent and send the relevant email(s).

    If you do this in the application you can hold the previous value in a variable, check the new one on save and then send the email if the difference is high enough. That's a lot cleaner (and easier) that the SQL solution.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Brussel

    SSC Rookie

    Points: 34

    Yes, you can implement it in the ERP system. But timing and cost price. Because change an ERP system cost a lot of money.

    Some other guy post this text. But when I want to configure the mail then I get the message 'Not implemented'.

    I'm scared to configure the mail. So I also read that you can save the original record to another file. But what is the code ?

    ************************************************************

    You need to create trigger using CREATE TRIGGER statement

    like

    CREATE TRIGGER TriggerName

    ON TableName

    AFTER UPDATE

    AS

    BEGIN

    IF EXISTS(

    SELECT 1

    FROM INSERTED i

    JOIN DELETED d

    ON d.PK = i.PK

    WHERE ABS(d.costprice - i.cosetprice) > d.costprice * .01

    )

    EXEC sp_sent_dbmail ....

    END

    Here PK would be the primary key of the table

    And sp_sent_dbmail is the procedure used for sending email notifications

    You've to enable database mail feature for using this

    see

    https://www.sqlshack.com/configure-database-mail-sql-server/

     

  • Phil Parkin

    SSC Guru

    Points: 243485

    I'm not a fan of triggers, so my initial thought would be to create a SQL Agent job which runs periodically (hourly? daily?), looking at all rows in the table where DateModified >= LastJobRunDate.

    Any rows satisfying this would be checked against the validation rules & an e-mail sent out with details of all, or any, which do not satisfy them.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Brussel

    SSC Rookie

    Points: 34

    And do you mean with 'checked against rules' manually or can you configure the validation ?

    If yes how can you do that ?

    Thx

  • Phil Parkin

    SSC Guru

    Points: 243485

    I mean by running one or more queries which look for validation issues & passing the results of those queries to sp_send_dbmail.

    There are many ways of doing it. Here is one:

    Create a stored procedure

    Create a temp table in the stored proc. This will contain any and all validation results. It can have as many or as few columns as you like, but I'd suggest something like (NameOfTest, RowDetails, TestResults).

    Your proc runs the tests and inserts the results (ie, the rows which fail the tests) to the temp table.

    At the end of the proc, if rows exist in the temp table, use sp_send_dbmail to send the contents of the temp table to the nominated recipients.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Thom A

    SSC Guru

    Points: 98219

    I think the problem here is, Phil, that the Op doesn't have historical data stored (if they did this would be trivial). As you could easily just use LAG to inspect the previous value of the row. So the OP needs a way of checking what the old and new values are. As a result they want to use a trigger to inspect the values in the inserted and deleted objects to see if the value has changed enough to require an email to be sent.

    I agree, I'm not a fan of triggers, and simply checking the (current?) row's value against the last value would be far easier. if they don't have a history then i would suggest they use a trigger to create the history and then they can do as you say, and check against the last value.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Phil Parkin

    SSC Guru

    Points: 243485

    Thom, if that's the case, you are right.

    But for the examples provided, I can't see how it's required (excepting, possibly, the change in cost price ā€“ though any half-decent application would track a history of changes as important as this).

    For example, checking for rows where

    (cost price + transport + packing cost - discount) >= (sales price * 1.4) or <= (sales price / 1.4 )

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Thom A

    SSC Guru

    Points: 98219

    Phil Parkin wrote:

    Thom, if that's the case, you are right. But for the examples provided, I can't see how it's required (excepting, possibly, the change in cost price ā€“ though any half-decent application would track a history of changes as important as this). For example, checking for rows where (cost price + transport + packing cost - discount) >= (sales price * 1.4) or <= (sales price / 1.4 )

    That's the thing, their one SQL query heavily implies they are checking old versus new prices:

    SELECT 1
    FROM INSERTED i
    JOIN DELETED d ON d.PK = i.PK
    WHERE ABS(d.costprice - i.cosetprice) > d.costprice * .01

    But you're right, if an application doesn't have price tracking, then that is an even bigger concern. For auditing perspectives you'd think that's a must (we certainly have to keep rating details for Insurance for years).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 9 posts - 1 through 9 (of 9 total)

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