SQL Developer Trigger for theatre database

Question

I am new to SQL and I am trying to create a trigger for my theatre database project for school. A theatre has a number of seats. For example the Apollo Theater has got 2100 seats. A show is visited by guests. I'd like to create a trigger, which checks whether the number of guests is greater than the number of seats and corrects it. The following code is only for the Apollo Theatre, I've got 2 more theatres in the database. If the code works I will add them on my own :)

create or replace trigger seats
before insert on show
for each row
when (old.number_of_guests is not null)
declare name_theatre show.name_theatre%TYPE;
begin
    if name_theatre = 'Apollo Theatre' and :new.guests > 2100
    then :new.number_of_guests := 2100;
    end if;
end;

The code compiles, but if I insert a show with 2200 guests the trigger doesn't change it to 2100. Thanks for helping me :)

Answer 1

There is no old in insert trigger and all values must use :new. And you are declaring variable which is not assigned any value. That variable is not at all requited.

create or replace trigger seats
before insert on show
for each row
begin
    if :new.name_theatre = 'Apollo Theatre' and :new.guests > 2100
    then :new.number_of_guests := 2100;
    end if;
end;