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

Question

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 pointers for it would be appreciated.

Thanks, -Adi

Florin Ghita · Accepted Answer · 2011-09-09 11:08:21Z

2

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.

edited Sep 9, 2011 at 11:08

answered Sep 9, 2011 at 10:47

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

adi Over a year ago

tHANKS ! i needed to insert it into dummy table every time not just (-1). And first thing worked ! And one more thing, how to change no-of-people to 0 when original number of people is any negative number ? and can we insert it as null in new dummy table ? column type is number.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related