oracle trigger problem

[sunsail]

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

 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;

[rajavu]

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 .

[tyro]

Trigger is absolutely fine. Have a look

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>