CODEWITHSUNDEEP
sqloracle-databasetriggers
Kevin
Kevin

Reputation: 862

SQL Trigger is not working properly because of 'invalid trigger specification'

I'm working on a table called Employees where first_name and last_name are stored (as varchar). I created the trigger below to check if a combination of the first and last name already exists:

CREATE OR REPLACE TRIGGER VALIDATE_NAME 
BEFORE INSERT ON EMPLOYEES 
IF (SELECT count(*) FROM employees 
     WHERE firs_name = new.first_name AND LAST_NAME = new.last_name) = 0
  BEGIN
  END
ELSE IF (SELECT count(*) FROM employees
          WHERE firs_name = new.first_name AND LAST_NAME = new.last_name) >= 1
  BEGIN
    RETURN FALSE;
  END;

However I receive an error message Invalid trigger specification, which I don't understand. Could someone clarify what I'm doing wrong? I have removed the content of the first BEGIN/END as it just stores the new information like it is supposed to.

P.S.: This is for a theoretical case studie, I know the proper way to check for duplicate entries is not using first and last name as two people may share the same name. I know that, so please don't start about it :-)

Upvotes: 0

Views: 48

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30845

Your trigger in its current form doesn't make much sense. A trigger is not a function, so you cannot return a value from it.

Also, your trigger is not a row-level trigger, but a statement-level one, so you cannot reference the new values (since your trigger is fired once per statement, not per row) - a row-level trigger has to be declared as FOR EACH ROW.

The proper way to ensure uniqueness is not a trigger, but a UNIQUE constraint (see AskTom on using triggers) :

ALTER TABLE employees ADD CONSTRAINT employees_name_uq UNIQUE(firstname, lastname);

Upvotes: 1

Related Questions