Invalid identifier when creating trigger

Question

When I try to create this trigger:

CREATE OR REPLACE trigger T25
BEFORE DELETE ON employer
FOR EACH ROW

DECLARE
vnbr number;
BEGIN

SELECT *  into vnbr from employer where role="utilisateu";
if(:old.role=vnbr) THEN

RAISE_APPLICATION_ERROR(-20001,'impossible de supprimer ce role');
END IF;
END;
/

I get this error :

PL/SQL: SQL Statement ignored
PL/SQL: ORA-00904: "utilisateu": invalid identifier

Please read the section "Literals" in the chapter "Basic Elements of Oracle SQL" in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#SQLRF00217 — , Jun 02 '15 at 07:20
`vnbr` is a **scalar** variable, not a **collection** variable. So, `select * into vnbr` will fail. — Lalit Kumar B, Jun 02 '15 at 07:31

Sachu · Answer 1 · 2015-06-02T07:29:56.263

first the literals should be enclosed in '

SELECT *  into vnbr from employer where role='utilisateu';

Also show us the employer table it contain only one value?. You are selecting * into vnbr(number) variable. You should specify the column name from where you are taking and inserting into vnbr. Also the query will fail if condition is true more than one row because it will return more than one value and you are trying to save it into a one variable.

Invalid identifier when creating trigger

1 Answers1