Post reply

how to create triggers with delete and update

  • November 21, 2019 at 5:38 pm

    #3699720

    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.

  • November 21, 2019 at 6:07 pm

    #3699724

    Why not just use MERGE? No triggers are required.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 21, 2019 at 6:13 pm

    #3699727

    its requirement.

  • November 21, 2019 at 6:24 pm

    #3699730

    mcfarlandparkway wrote:

    its requirement.

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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 21, 2019 at 6:26 pm

    #3699731

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

  • November 21, 2019 at 6:47 pm

    #3699749

    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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 21, 2019 at 9:42 pm

    #3699816

    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;

  • November 22, 2019 at 3:39 am

    #3699867

    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 5 years 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.

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

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

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