identifier ‘NEW.CITY_POPULATION’ must be declared

I am trying to declare a oracle trigger that will update the city name when the city population reaches 0 but I am getting this error.

LINE/COL  ERROR
--------- -------------------------------------------------------------
3/5       PL/SQL: Statement ignored
3/11      PLS-00201: identifier 'NEW.CITY_POPULATION' must be declared
Errors: check compiler log```

here is my code

```CREATE TRIGGER tr_Town_Death AFTER UPDATE 
ON Cities
FOR EACH ROW BEGIN

    IF NEW.city_population = 0 THEN
        UPDATE Cities 
        SET city_name = 'Ghost town' 
        WHERE city_id = NEW.city_id;
    END IF;
END tr_Town_Death;```

Answer

1. If you’re going to reference the :new pseudo-record in a trigger body, it needs to be prefixed with a colon.
2. A trigger on cities generally cannot query the cities table. In this case, it appears that you want to modify the data in the row the trigger is firing for so you’d just want to update the :new pseudo-record.
3. Since you want to modify the row that caused the trigger to fire, you’ll want it to be a before update trigger.

When we make those changes, you’ll end up with something like this

CREATE TRIGGER tr_Town_Death 
  BEFORE UPDATE 
  ON Cities
  FOR EACH ROW 
BEGIN
    IF :NEW.city_population = 0 
    THEN
        :new.city_name := 'Ghost town';
    END IF;
END tr_Town_Death;