Check value in before delete oracle trigger and then insert in another table

Currently we have a trigger on tbl_number. It is as follows,

create or replace TRIGGER TRIGGER_EXAMPLE
BEFORE DELETE
ON TBL_NUMBER
FOR EACH ROW
BEGIN


-- Insert record into TBL_NUMBER _DUMMY table
INSERT INTO TBL_NUMBER _DUMMY
 ( NAME, NO_OF_PEOPLE)
  VALUES
 ( :old.NAME, :old.NO_OF_PEOPLE)
 END;

Now we want to modify trigger and check value NO_OF_People in original table tbl_number and if it is (-1), we want to put 0 (or null) in column NO_of_People of table tbl_number_dummy.

Any pointer开发者_如何学JAVAs for it would be appreciated.

Thanks, -Adi

INSERT INTO TBL_NUMBER_DUMMY
 ( NAME, NO_OF_PEOPLE)
  VALUES
 ( :old.NAME, decode(:old.NO_OF_PEOPLE, -1, 0, :old.NO_OF_PEOPLE)
 END;

Or, if you want to insert into TBL_NUMBER _DUMMY only if NO_OF_PEOPLE = -1,

CASE WHEN :old.NO_OF_PEOPLE = -1 THEN
INSERT INTO TBL_NUMBER _DUMMY
 ( NAME, NO_OF_PEOPLE)
  VALUES
 ( :old.NAME, :old.NO_OF_PEOPLE)
ELSE null;
END;

UPDATE(response to comment):

INSERT INTO TBL_NUMBER_DUMMY
 ( NAME, NO_OF_PEOPLE)
  VALUES
 ( :old.NAME, case when :old.NO_OF_PEOPLE < 0 then 0; else :old.NO_OF_PEOPLE; end)
 END;

or, with decode

INSERT INTO TBL_NUMBER_DUMMY
 ( NAME, NO_OF_PEOPLE)
  VALUES
 ( :old.NAME, decode(sign(:old.NO_OF_PEOPLE), -1, 0, :old.NO_OF_PEOPLE)
 END;

:)

And yes you can insert null into a number column, except the situation when the column has a NOT NULL. constraint.