Good morning! Tell me, please, is it possible to write a trigger that inserts only non-duplicate entries? I can not. If anyone faced with such a problem, help.

  • May be more convenient unique hang? Or necessarily trigger? - Vitaly Kustov
  • No, the trigger is not so important, the main result is prapar

2 answers 2

No trigger is needed here. It is enough to put a unique index on the table. Here is the command:

create unique index <имя индекса> on <имя таблицы> (<поле1>, <поле2>, <поле3>)

field1, field2, field3 are the fields by which the uniqueness of a record is determined.

Another solution depends on the requirements of the problem. If you need to insert the first record of the duplicate or need guaranteed uniqueness, then you need a variant with a unique index. The disadvantage of this option is that you need to handle errors when inserting duplicate records. In all other cases - the UPDATE OR INSERT .

  • Well, better that way. Well, imagine, what is the load on the database, when it will do a search operation on this parameter before insertion?! O_o - Anton Mukhin
  • The base will not be very large, because I plan to upload obsolete information to the archive and, if absolutely necessary, take data from it. Thanks for the valuable advice, I wrote a procedure that does not insert data if there is a duplicate of the incoming record. How can I handle errors when inserting duplicate records? I just didn’t do this yet, I’ll suddenly press it, so at least I’ll know what to do. - prapar

Another option is to use the UPDATEorINSERT command instead of the INSERT .

Description