how to create triggers with delete and update

  • mcfarlandparkway

    SSCertifiable

    Points: 7621

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

    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.

  • mcfarlandparkway

    SSCertifiable

    Points: 7621

    its requirement.

  • Phil Parkin

    SSC Guru

    Points: 243849

    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.

  • mcfarlandparkway

    SSCertifiable

    Points: 7621

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

  • Phil Parkin

    SSC Guru

    Points: 243849

    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.

  • 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: 995126

    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 2 weeks, 4 days 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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code 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