Mail when value of field < or > paramater%

  • Brussel

    SSC Enthusiast

    Points: 128

    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: 98460

    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 Enthusiast

    Points: 128

    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: 243861

    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 Enthusiast

    Points: 128

    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: 243861

    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: 98460

    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: 243861

    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: 98460

    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.

  • Brussel

    SSC Enthusiast

    Points: 128

    Hello,

    Here I again.

    Sorry for late respons but I've check something out.

    I'm very new and have no experience. So, if possible, keep it simple 🙂

    This is what I want but I don't now what the best method is for getting this result.

    Reporting services, trigger, ...... ????

    Note : Table 'Orders' is a table where it's not possible for the ERP developpers to check the value.

    Description :

    There is a SQL table 'Orders'. This table has a lot of fields.

    When a new record, automatically, is created in the file then I want to test :

    Has field "Type" the value 02 and is calculation : 1-((pur_price/qty_pur)/(val_pur/val_qty)) >0,10 of <-0,10 then a mail has to be sent to xxx@xxx.com

    All the fields are in 1 record. So no check to other record.

    Table structue :

    Count   |  Type  |  Pur_price  | Qty_pur  | Val_pur  |  Val_qty | ......

    125           02          15                 10              52              5

    Now a mail has to be sent.

    I hope the picture is clear and that somebody can help me.

     

  • ScottPletcher

    SSC Guru

    Points: 98218

    A trigger is actually perfect for this situation.  For the email, it's probably best to add row(s) to a table or queue, then construct and send the emails based on what's in the table/queue, for example, by using a job to process them.

    Something like this:

    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO
    CREATE TRIGGER Orders__TR_INS
    ON dbo.Orders
    AFTER INSERT
    AS
    SET NOCOUNT ON;

    INSERT INTO dbo.Orders_email (
    PK, create_date, status
    )
    SELECT PK, GETDATE() AS create_date, 1 AS status /*1=emails needs sent/is pending*/
    FROM dbo.Orders O
    INNER JOIN inserted I ON I.PK = O.PK
    WHERE (1-((pur_price * 1.0 / qty_pur) / (val_pur * 1.0 / val_qty))) BETWEEN 0.0 AND 10.0

    /*naturally you'd need to create this job and related code to construct and send emails.*/
    EXEC msdb.dbo.sp_start_job @job_name = 'Send_Orders_Email';
    /*end of trigger code*/
    GO

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Brussel

    SSC Enthusiast

    Points: 128

    Hello,

    I've a few questions :

    1. How can I create a trigger ?

    I go the SQL server. Databases - productiondatabase name - tables - dbo.Orders - on folder 'Trigger' right click - select 'New trigger'. But when I save the SQL code I get a pop-up to save the code in *.sql. So I have to give a name. When I do that I don't see the filename under folder 'Trigger'. I mean I thought that I see the filename here. What do I wrong ?

    2.  Users get a warning ?

    I was busy with the trigger on the production data because I thought I can't do anything wrong but suddenly the users call me they get an error. They were working on the ERP system and enter orders. Do I need to take action or what do I have to do the users don't get this message ?

    3.  Sent a mail

    On internet I wrote that you have to configure your database if you want to sent a mail. It's a complicated configuration.

    But I also found this code.

    CREATE TRIGGER reminder2

    ON Sales.Customer

    AFTER INSERT, UPDATE, DELETE

    AS

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'AdventureWorks2012 Administrator',

    @recipients = 'danw@Adventure-Works.com',

    @body = 'Don''t forget to print a report for the sales force.',

    @subject = 'Reminder';

    GO

    Is this also a correct code ?

    4.  Table dbo.orders_email

    In your code I see, I think, table dbo.orders_email. I think I have to create this table manually with the fields PK, create_date and status. Or I'm wrong ?

    5.   Mail

    If I have problems to sent the mail or there other options ? Put a file in a certain folder or ......?

    And I want to thank you for the code.

    • This reply was modified 4 months, 1 week ago by  Brussel.

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

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