1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

oracle trigger problem

Discussion in 'SQL PL/SQL' started by sunsail, Dec 4, 2008.

  1. sunsail

    sunsail Guest

    Hi,
    I am just trying to create a trigger to make a small test.however I cannot make it what is missing? I put the sql below
    Thanks
    Code (Text):

     create table myaudit(
           id VARCHAR2(4 BYTE)         NOT NULL,
           old_value VARCHAR2(40 BYTE),
           new_value VARCHAR2(40 BYTE)
        );

    create table Employee(
          ID                 VARCHAR2(4 BYTE)         NOT NULL,
          First_Name         VARCHAR2(10 BYTE),
          Last_Name          VARCHAR2(10 BYTE),
          Start_Date         DATE,
          End_Date           DATE,
          Salary             Number(8,2),
          City               VARCHAR2(10 BYTE),
          Description        VARCHAR2(15 BYTE)
       );


    CREATE OR REPLACE TRIGGER before_employee_salary_update
    BEFORE UPDATE OF salary
    ON employee
    FOR EACH ROW WHEN (new.salary < old.salary )
     BEGIN
       --dbms_output.put_line('id = ' || : old.id);
        --dbms_output.put_line('Old salary = ' || : old.salary);
       --dbms_output.put_line('New salary = ' || :new.salary);

      INSERT INTO myaudit (
        id, old_value, new_value
      ) VALUES (
       : old.id, : old.salary, :new.salary
      );
      END before_employee_salary_update;


     
    update employee set salary = 4
    where id=02;

    select * from Employee;

    select * from myaudit;
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You didn't mention what is wrong with the trigger.

    What were the previous values in Employee ?
    What were the values after updation in Myaudit ?

    Trigger looks fine .And I guess you committed the update transaction in Employee to reflect the changes in Myaudit . Please show us the test case to piint out where exactly went wrong .
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Trigger is absolutely fine. Have a look

    Code (Text):

    SQL> create table myaudit(
      2         id VARCHAR2(4 BYTE)         NOT NULL,
      3         old_value VARCHAR2(40 BYTE),
      4         new_value VARCHAR2(40 BYTE)
      5      );

    Table created.

    SQL> create table Employee(
      2        ID                 VARCHAR2(4 BYTE)         NOT NULL,
      3        First_Name         VARCHAR2(10 BYTE),
      4        Last_Name          VARCHAR2(10 BYTE),
      5        Start_Date         DATE,
      6        End_Date           DATE,
      7        Salary             Number(8,2),
      8        City               VARCHAR2(10 BYTE),
      9        Description        VARCHAR2(15 BYTE)
     10     );

    Table created.

    SQL> CREATE OR REPLACE TRIGGER before_employee_salary_update
      2  BEFORE UPDATE OF salary
      3  ON employee
      4  FOR EACH ROW WHEN (new.salary < old.salary )
      5   BEGIN
      6     --dbms_output.put_line('id = ' || : old.id);
      7      --dbms_output.put_line('Old salary = ' || : old.salary);
      8     --dbms_output.put_line('New salary = ' || :new.salary);
      9  
     10    INSERT INTO myaudit (
     11      id, old_value, new_value
     12    ) VALUES (
     13     :old.id, :old.salary, :new.salary
     14    );
     15    END before_employee_salary_update;
     16  /

    Trigger created.

    SQL> INSERT INTO Employee (ID,First_Name,Salary) VALUES ('1','TYRO',10000);

    1 row created.

    SQL> update employee set salary = 5000
      2  where id=1;

    1 row updated.


    SQL> select * from myaudit;

    ID   OLD_VALUE
    ---- ----------------------------------------
    NEW_VALUE
    ----------------------------------------
    1    20000
    5000

    SQL>