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

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.

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.