how to create triggers with delete and update

  • mcfarlandparkway

    SSCertifiable

    Points: 7642

    how to create triggers with delete and update

    i have a table called dbo.OrderDetail with columns (OrderId,OrderDate,OrederArea,OrderCode,CreateDate] and created audit table with new column called flag that opearted Del and Upd to handle update and delete operations;

    My SSIS package pulls data from file to stage table. after this process it will procedure that inserts records into dbo.Order from temp.Order stg where not exits (select 1 from dbo.Order Od

    where stg.OrderId = Od.OrderId

    and    stg.OrderDate = Od.OrderDate

    and stg.OrederArea = Od.OrderArea

    and  stg.OrderCode = Od.OrderCode)

    Probelm is that form the source we may get duplicates. if there is already data existing with one order id and order date , if the new data comes over it should update exisitng record.

    if all 4 columns data exists already and the new data matched with exisitng it shoudl delete using trigger.

  • Phil Parkin

    SSC Guru

    Points: 244578

    Why not just use MERGE? No triggers are required.

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • mcfarlandparkway

    SSCertifiable

    Points: 7642

    its requirement.

  • Phil Parkin

    SSC Guru

    Points: 244578

    mcfarlandparkway wrote:

    its requirement.

    Well, unless there are valid reasons, it's a dumb one.

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • mcfarlandparkway

    SSCertifiable

    Points: 7642

    Its kind of dump ; yeah . how can we merge it?

  • Phil Parkin

    SSC Guru

    Points: 244578

    I just reread your original post a little more closely. Your duplicates are in the source, not in the target, is that correct?

    If so, the best way of handling this (in my opinion) is to remove the unwanted duplicates before attempting to merge in to the target.

    The target tables should have appropriate PKs, FKs and unique indexes defined which help ensure the integrity of your data and remove the possibility of any duplicates being loaded.

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Sumathi

    SSC Rookie

    Points: 28

    Remove duplicates using CTE as below and then you can insert records as per your existing process.

    --Removing duplicates

    ;WITH Duplicates AS (

    SELECT

    OrderId,OrderDate,OrederArea,OrderCode,

    ROW_NUMBER() OVER (

    PARTITION BY

    OrderId,OrderDate,OrederArea,OrderCode

    ORDER BY

    CreateDate desc

    ) row_num

    FROM

    temp.Order

    )

    delete from Duplicates WHERE row_num > 1;

  • Jeff Moden

    SSC Guru

    Points: 996636

    mcfarlandparkway wrote:

    if all 4 columns data exists already and the new data matched with exisitng it shoudl delete using trigger.

    There's a little bit of ambiguity in that statement.  Delete from where????  The source table or the destination table?  I can't see deleting data from the destination table this way but it's been a little difficult to understand what you want from your partial descriptions.

    What I'm thinking is that you want a partial "upsert".

    1.  If a row exists in the destination table and there are no rows in the source table that match by order number and date, do nothing.
    2. If a row exists in the destination table and there is a row in the source table that matches by order number and date, update the row in the destination table.
    3. If a row exists in the source table that has no matching row (by order number and date) in the desitination table, insert the row into the destination table.

    Is that correct or is the really something that needs to be done with the ambiguous statement that I pointed out?

    • This reply was modified 7 months, 2 weeks ago by  Jeff Moden.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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