trigger

2947022

Hi All,

I am using

Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0

I am using Oracle HR schema

I am trying to learn triggers

SQL> CREATE OR REPLACE TRIGGER BIUFER_EMPLOYEES_DEPARTMENT_ID

  2  BEFORE INSERT OR UPDATE

  3  OF DEPARTMENT_ID

  4  ON EMPLOYEES

  5  REFERENCING OLD AS OLD_VALUES

  6  NEW AS NEW_VALUES

  7  FOR EACH ROW

  8    WHEN (NEW_VALUES.DEPARTMENT_ID <> 80)

  9      BEGIN

10        :NEW_VALUES.COMMISSION_PCT :=0 ;

11        END;

12  /

Trigger created

This trigger will fire when any insert on employees table when the departemnt_id is not equal to 80 for new values

This trigger will fire when any update  on employees table when the departemnt_id is not equal to 80 for new values

For the new values the COMMISSION_PCT will be changed to zeo if the trigger fires.

Please let me know my understanding is correct

MLBrown

SQL> CREATE OR REPLACE TRIGGER BIUFER_EMPLOYEES_DEPARTMENT_ID
  2  BEFORE INSERT OR UPDATE
  3  OF DEPARTMENT_ID
  4  ON EMPLOYEES
  5  REFERENCING OLD AS OLD_VALUES
  6  NEW AS NEW_VALUES
  7  FOR EACH ROW
  8    WHEN (NEW_VALUES.DEPARTMENT_ID <> 80)
  9      BEGIN
10        :NEW_VALUES.COMMISSION_PCT :=0 ;
11        END;
12  /

Trigger created

This trigger will fire when any insert on employees table when the departemnt_id is not equal to 80 for new values

This trigger will fire when any update  on employees table when the departemnt_id is not equal to 80 for new values

The trigger is going to fire on any insert or update of the DEPARTMENT_ID on the EMPLOYEES table regardless of the value.  The COMMISION_PCT will only be set if the DEPARTMENT_ID is not 80.

John Stegeman

it seems to be relatively correct, yes. What happened when you tested it?

You may want to consider what happens if someone inserts or updates department_id to NULL (hint NULL is not equal to 80 and it's also not NOT EQUAL to 80)