Forum Stats

  • 3,827,745 Users
  • 2,260,818 Discussions
  • 7,897,364 Comments

Discussions

trigger

2947022
2947022 Member Posts: 59
edited May 21, 2015 10:34AM in SQL & PL/SQL

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


Tagged:

Answers

  • MLBrown
    MLBrown Member Posts: 402 Bronze Badge
    edited May 21, 2015 9:57AM
    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.

    2947022
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 21, 2015 10:34AM

    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)

    2947022
This discussion has been closed.