Insert trigger to validate unique constraint

Question

I have the following trigger which gets fired when updated becomes Y. I would like to insert the old values only if values do not exist in my_hist table. If the same record exists then it should not insert. For this best way is whether to create a constraint to check uniqueness in my_hist table or check this condition in trigger? If so how could I do this in trigger?

Or is it possible to check the unique constraint of my_hist table in trigger so that it will not insert duplicate records.

CREATE OR REPLACE TRIGGER mytrig
    AFTER UPDATE
    ON mytab
    FOR EACH ROW
WHEN (
new.updated = 'Y'
      )
BEGIN
    INSERT INTO my_hist
      VALUES   (
                        :old.id,
                        :old.no,                        
                        :old.start_date,
                        :old.end_date,
                        SYSDATE
                  );
END mytrig;
/

Answer 1

If you want to enforce uniqueness, use a unique constraint. That's always going to be preferrable to coding something yourself.

You can check whether the row already exists as part of your INSERT statement

INSERT INTO my_hist( id, 
                     no, 
                     start_date, 
                     end_date, 
                     some_other_column )
  SELECT :old.id, 
         :old.no, 
         :old.start_date,
         :old.end_date,
         sysdate
    FROM dual
   WHERE NOT EXISTS( SELECT 1
                       FROM my_hist
                      WHERE my_hist.id = :old.id
                        AND my_hist.no = :old.no
                        AND my_hist.start_date = :old.start_date
                        AND my_hist.end_date   = :old.end_date );

That will not work, however, in a multi-user environment where multiple sessions might insert the same row at the same time.

Answer 2

You can change your insert to the following in order to check if a duplicate exists already. The select assumes that the primary key of my_hist is id and start_date, change the where not exists clause to use only the primary key values if they're different.

insert into my_hist
select :old.id, :old.no, :old.start_date, :old.end_date, sysdate
  from dual
 where not exists ( select 1
                      from my_hist
                     where id = :old.id
                       and start_date = :old.start_date
                           )

However, this requires an index scan so it's not that optimal.

Instead let the exception get raised. It's explicitly named: dup_val_on_index. If this does get raised then simply ignore it.

BEGIN
    INSERT INTO my_hist
      VALUES   (
                        :old.id,
                        :old.no,                        
                        :old.start_date,
                        :old.end_date,
                        SYSDATE
                  );
-- ignore an exception that get's raised if a duplicate value
-- gets inserted. 
EXCEPTION WHEN DUP_VAL_ON_INDEX then null;
END mytrig;