CODEWITHSUNDEEP
oracle11gtriggers
Andrew Ooi
Andrew Ooi

Reputation: 13

Errors in triggers

This is my trigger:

CREATE OR REPLACE TRIGGER trg_CheckStaffID
BEFORE INSERT ON ASSIGN
FOR EACH ROW

BEGIN
DECLARE id integer := 0;

    SET id := (select count(*) from (select staffid from staff where staffid ='T2');


    IF (id=0) THEN
        RAISE_APPLICATION_ERROR(-20000,'Please Enter A Valid Staff ID');
    END IF;

END;
/

And this is the error message I get:

PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + case mod new not null

continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date
pipe

PLS-00103: Encountered the symbol "IF"
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map

Upvotes: 1

Views: 30

Answers (1)

Littlefoot
Littlefoot

Reputation: 143163

Invalid syntax on different places; DECLARE section should be before BEGIN. There's no SET command in PL/SQL.

Here's code that compiles; whether it does what you meant, can't tell. (I'm creating dummy tables, just to make sure that trigger creation wouldn't fail).

SQL> create table assign (id number);

Table created.

SQL> create table staff (staffid varchar2(2));

Table created.

SQL> create or replace trigger trg_checkstaffid
  2    before insert on assign
  3    for each row
  4  declare
  5    id integer := 0;
  6  begin
  7    select count(*)
  8      into id
  9      from (select staffid
 10            from staff
 11            where staffid ='T2');
 12
 13    if id = 0 then
 14       raise_application_error(-20000, 'Please Enter A Valid Staff ID');
 15    end if;
 16  end;
 17  /

Trigger created.

SQL>

Upvotes: 1

Related Questions