How to stop duplicate records in Oracle SQL Developer?

Data and Databases
1 
I'm stuck trying to make a trigger that stops duplicate entries in Oracle SQL Developer (11g XPRESS). My code won't compile and I can't figure out why. Here's what I've got:

CREATE OR REPLACE TRIGGER prevent_dupes
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
  IF EXISTS (
    SELECT 1 FROM my_table
    WHERE col1 = :NEW.col1 AND col2 = :NEW.col2
  ) THEN
    RAISE_APPLICATION_ERROR(-20001, 'Duplicate entry not allowed');
  END IF;
END;

Can anyone spot what's wrong? I'm pretty new to Oracle triggers. Thanks!
2

hey there! have u tried using a unique constraint instead of a trigger? it’s usually simpler and more efficient. but if u really need a trigger, maybe check if ur table name is correct in the EXISTS clause? sometimes that trips me up. what error are u getting when it won’t compile?

3

yo, have u tried using merge? it handles dupes neat, like:
MERGE INTO my_table t
USING (SELECT :NEW.col1 as col1, :NEW.col2 as col2 FROM dual) s
ON (t.col1=s.col1 AND t.col2=s.col2)
WHEN NOT MATCHED THEN INSERT(col1,col2) VALUES (s.col1,s.col2);
might work.

4

Your approach using a trigger is valid, but there might be a more efficient solution. Consider creating a unique index on the columns you want to prevent duplicates for. This method is generally faster and requires less maintenance. Here’s an example:

CREATE UNIQUE INDEX prevent_dupes_idx ON my_table (col1, col2);

This index will automatically prevent duplicate entries for the specified columns. If you still prefer using a trigger, ensure you have the necessary privileges to create triggers on the table. Also, double-check that the table name in your SELECT statement matches exactly with your actual table name, including case sensitivity if applicable. The error message from the compilation attempt would provide more specific information about what’s causing the issue.