CODEWITHSUNDEEP
oracle-databaseplsqloracle11gdatabase-trigger
Mattreex
Mattreex

Reputation: 199

Trying to create a trigger but get error PLS-00103

create or replace trigger newcontract
before insert on contract
declare numcon int;
for each row
begin
    select contractcount 
    into numcon from task
    where task.taskid = old.taskid;
    if numcon < 3 then
        insert into contract values(taskid, workerid, payment);
    else
        dbms_output.put_line('task is full');
    end if;
end;

Gives this cryptic error

Error(1,5): PLS-00103: Encountered the symbol "FOR" when expecting one of the following:     begin function pragma procedure subtype type <an identifier>    <a double-quoted delimited-identifier> current cursor delete    exists prior

The record being inserted into contract should not be inserted if contract count for that task is about 2. So I need to check the value of contractcount for each record being inserted. I use a select statement to get the value, but I get this error.

Upvotes: 1

Views: 68

Answers (1)

VBoka
VBoka

Reputation: 9083

You have more than one problem here:

  1. declare section(the part of the trigger where you declare your variables) goes after the for each row part
  2. OLD and NEW values are "accessed" like this : :new.column_name and :old.column_name
  3. The :old value in before insert trigger is always null because you are inserting a new value, there is no old value, only new value.

  4. If you want to prevent insert if some value is smaller than 3 then you can do it like this:

    create or replace trigger newcontract
before insert on contract 
for each row

declare 

numcon int;

begin

    select contractcount 
    into numcon 
    from task
    where task.taskid = :new.taskid;

    if numcon < 3 then
        raise_application_error(-20000, 'Task is full');
    end if;

end;
/

Here is a small demo

For more info please do add some more detailed description and some sample data where you show us what kind of data you want to be able to insert and why and what kind of data you do not want to insert and why.

Upvotes: 2

Related Questions