CODEWITHSUNDEEP
oracle-databaseplsqltriggersduplicates
Richard Cane
Richard Cane

Reputation: 33

PL/SQL trigger to prevent duplicates

I have a table called TBLAPPLICATION which holds data specifying an individual's ID number and a JobID of the job they have applied for. Each ID number can have an unlimited number of applications, providing the JobID is different every time, thus having no duplicate applications.

create or replace
   TRIGGER trg_duplicateapplication BEFORE INSERT ON tblapplication FOR EACH ROW

BEGIN
   IF :NEW.studentrecordnumber_fk_nn = :OLD.studentrecordnumber_fk_nn THEN
      IF :NEW.jobid_fk_nn = :OLD.jobid_fk_nn
         THEN RAISE_APPLICATION_ERROR( -20003, 'Error: duplicate application. You have already applied for this position.');
      END IF;
   END IF;
END;

So the above code doesn't work, and I wish it would. Could anyone please highlight my mistake? :)

Upvotes: 0

Views: 4460

Answers (3)

diaphol
diaphol

Reputation: 127

I am not sure that in your table TBLAPPLICATION which identifier is unique (maybe JobID?) and which you want you not to be duplicated (maybe studentrecordnumber_fk_nn?). But I created a script to prevent duplication on studentrecordnumber_fk_nn. And in my example “alphabet” I wrote a totally similar script to prevent the duplication: you cannot insert a letter which was inserted into the table earlier.

I hope it will help. z

CREATE OR REPLACE TRIGGER trg_duplicateapplication
BEFORE INSERT
ON tblapplication
FOR EACH ROW
DECLARE
counter integer;
BEGIN
  SELECT * INTO counter FROM
      (SELECT COUNT(rownum) FROM tblapplication a
        WHERE a.studentrecordnumber_fk_nn = :new.studentrecordnumber_fk_nn);
  IF counter = 1 THEN
  RAISE_APPLICATION_ERROR( -20003,
  'Error: duplicate application. You have already applied for this position.');
  END IF;
END;
/


––The Alphabet


CREATE TABLE alphabet
(letter VARCHAR2(2));
INSERT INTO alphabet VALUES ('A');
INSERT INTO alphabet VALUES ('B');
INSERT INTO alphabet VALUES ('C');
INSERT INTO alphabet VALUES ('D');

CREATE OR REPLACE TRIGGER insertvalue
BEFORE INSERT
ON alphabet
FOR EACH ROW
DECLARE counter INTEGER;
BEGIN
    SELECT * INTO counter FROM
      (SELECT COUNT(rownum) FROM alphabet a WHERE a.letter = :new.letter);
    IF counter = 1 THEN
      RAISE_APPLICATION_ERROR(-20012,'Duplicated value');
    END IF;
END;
/

Upvotes: 0

dariyoosh
dariyoosh

Reputation: 614

You can use the MERGE statement in order to verify each couple (id,application) before inserting in the table (to check whether it is already in the table).

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#SQLRF01606

Regards,

Dariyoosh

Upvotes: 0

user533832
user533832

Reputation:

As it stands, your trigger is comparing the inserted values (:NEW.studentrecordnumber_fk_nn etc) with a non-existent :OLD (:OLD has no meaning to an INSERT trigger—it's fields are always null).

That aside, this should almost certainly be accomplished by DRI instead of a trigger at all— how about a unique index on (studentrecordnumber_fk_nn, jobid_fk_nn)?

Upvotes: 8

Related Questions