Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    3

    Unanswered: Trigger that prevents duplicate rows

    Can someone please help me with a trigger that prevents duplicate rows upon insertion?

    I want it to be able to find a row that is already there and not insert it into the table.


    Below is what I currently have: This is written in DB2

    ---------------------------------------------------------------------
    CREATE TRIGGER Insert_records
    Before INSERT ON TEAMS_THIRD_PARTY_ABS_FILE_IMP
    REFERENCING new as nnn
    FOR EACH ROW
    MODE DB2SQL
    BEGIN
    IF NOT EXISTS ( SELECT TTPAFI_EMPLOYEE_ID FROM TEAMS_THIRD_PARTY_ABS_FILE_IMP IMP WHERE IMP.TTPAFI_EMPLOYEE_ID = nnn.TTPAFI_EMPLOYEE_ID )
    THEN
    INSERT INTO TEAMS_THIRD_PARTY_ABS_FILE_IMP (
    nnn.TTPAFI_ID,
    nnn.TTPAFI_JOB_NUMBER,
    nnn.TTPAFI_EMP_SSN,
    nnn.TTPAFI_SUB_SSN,
    nnn.TTPAFI_ABS_REASON,
    nnn.TTPAFI_CREATED_ON_DT,
    nnn.TTPAFI_CREATED_ON_TS,
    nnn.TTPAFI_START_DT,
    nnn.TTPAFI_END_DT,
    nnn.TTPAFI_START_TM,
    nnn.TTPAFI_END_TM,
    nnn.TTPAFI_JOB_STATUS,
    nnn.TTPAFI_AM_PM,
    nnn.TTPAFI_PERCENT_OF_DAY,
    nnn.TTPAFI_ABSENT_HOURS,
    nnn.TTPAFI_RAW_LINE,
    nnn.TTPAFI_PROCESSED_TS,
    nnn.TTPAFI_IMPORT_TS,
    nnn.TTPAFI_IMPORT_FILE_NAME,
    nnn.TTPAFI_PROCESSED_STATUS,
    nnn.TTPAFI_ERROR_MSG,
    nnn.TTPAFI_EMPLOYEE_ID,
    nnn.TTPAFI_SUB_PER_ID)
    VALUES ( SELECT
    TTPAFI_ID,
    TTPAFI_JOB_NUMBER,
    TTPAFI_EMP_SSN,
    TTPAFI_SUB_SSN,
    TTPAFI_ABS_REASON,
    TTPAFI_CREATED_ON_DT,
    TTPAFI_CREATED_ON_TS,
    TTPAFI_START_DT,
    TTPAFI_END_DT,
    TTPAFI_START_TM,
    TTPAFI_END_TM,
    TTPAFI_JOB_STATUS,
    TTPAFI_AM_PM,
    TTPAFI_PERCENT_OF_DAY,
    TTPAFI_ABSENT_HOURS,
    TTPAFI_RAW_LINE,
    TTPAFI_PROCESSED_TS,
    TTPAFI_IMPORT_TS,
    TTPAFI_IMPORT_FILE_NAME,
    TTPAFI_PROCESSED_STATUS,
    TTPAFI_ERROR_MSG,
    TTPAFI_EMPLOYEE_ID,
    TTPAFI_SUB_PER_ID
    FROM TEAMS_THIRD_PARTY_ABS_FILE_IMP imp
    WHERE
    nnn.TTPAFI_EMPLOYEE_ID = imp.TTPAFI_EMPLOYEE_ID
    and
    nnn.TTPAFI_START_DT = imp.TTPAFI_START_DT);
    END IF;
    END

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Normally, unique constraints are used for that. If you insist on doing it with a trigger, you should raise an exception when the duplicate is being inserted, instead of inserting another record when there is no duplicate.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If an exception condition as proposed by Nick is not an option, you could create a view over the table and then create an INSTEAD OF INSERT trigger on the view. The above condition can be put into the trigger body, i.e. if no duplicate is found, do the insert - otherwise, do nothing.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •