Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Dec 2008
    Posts
    99

    Unanswered: oracle trigger problem

    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 = ' || ld.id);
    --dbms_output.put_line('Old salary = ' || ld.salary);
    --dbms_output.put_line('New salary = ' || :new.salary);

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



    update employee set salary = 4
    where id=02;

    select * from Employee;

    select * from myaudit;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I can tell, there's nothing wrong about the trigger. It is the UPDATE statement that is wrong.

    As ID column is of a character datatype, you need to enclose its value into single quotes:
    Code:
    ... WHERE id = '02'
    (Make sure that there is an employee whose ID is '02'!).

    Now try again.

  3. #3
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by Littlefoot
    As far as I can tell, there's nothing wrong about the trigger. It is the UPDATE statement that is wrong.

    As ID column is of a character datatype, you need to enclose its value into single quotes:
    Code:
    ... WHERE id = '02'
    (Make sure that there is an employee whose ID is '02'!).

    Now try again.
    Hi,
    update employee set salary = 1
    where id='02';

    nothing has changed, did you succeed to run this test?

    I really got crazy.It must have worked.

    Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As I've said, everything seems to be OK:
    Code:
    SQL> create table myaudit
      2  (id varchar2(10),
      3   old_value number,
      4   new_value number);
    
    Table created.
    
    SQL> create or replace trigger bu_sal
      2    before update of sal on emp
      3    for each row
      4    when (new.sal < old.sal)
      5  begin
      6    insert into myaudit
      7      (id, old_value, new_value)
      8       values
      9      (:old.ename, :old.sal, :new.sal
     10      );
     11  end;
     12  /
    
    Trigger created.
    
    SQL> select sal from emp where ename = 'KING';
    
           SAL
    ----------
          5000
    
    SQL> update emp set sal = 2000 where ename = 'KING';
    
    1 row updated.
    
    SQL> select sal from emp where ename = 'KING';
    
           SAL
    ----------
          2000
    
    SQL> select * from myaudit;
    
    ID          OLD_VALUE  NEW_VALUE
    ---------- ---------- ----------
    KING             5000       2000
    
    SQL>

  5. #5
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by Littlefoot
    As I've said, everything seems to be OK:
    Code:
    SQL> create table myaudit
      2  (id varchar2(10),
      3   old_value number,
      4   new_value number);
    
    Table created.
    
    SQL> create or replace trigger bu_sal
      2    before update of sal on emp
      3    for each row
      4    when (new.sal < old.sal)
      5  begin
      6    insert into myaudit
      7      (id, old_value, new_value)
      8       values
      9      (:old.ename, :old.sal, :new.sal
     10      );
     11  end;
     12  /
    
    Trigger created.
    
    SQL> select sal from emp where ename = 'KING';
    
           SAL
    ----------
          5000
    
    SQL> update emp set sal = 2000 where ename = 'KING';
    
    1 row updated.
    
    SQL> select sal from emp where ename = 'KING';
    
           SAL
    ----------
          2000
    
    SQL> select * from myaudit;
    
    ID          OLD_VALUE  NEW_VALUE
    ---------- ---------- ----------
    KING             5000       2000
    
    SQL>

    so what am I facing for..? user rights?
    Thanks

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, it would be nice if you could do what I have done: copy/past YOUR SQL*Plus session. Don't forget to apply the [code] tags in order to preserve formatting (and make reading much easier).

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Assuming, that the test case in your initial post is complete, I got following results from the final statements:
    Code:
    SQL> select * from Employee;
    
    no rows selected
    
    SQL> update employee set salary = 4
      2  where id=02;
    
    0 rows updated.
    
    SQL> select * from Employee;
    
    no rows selected
    
    SQL> select * from myaudit;
    
    no rows selected
    As EMPLOYEE table contains zero rows (no INSERT statements were issued), the UPDATE changed zero rows (regardless the WHERE condition). So the row-level trigger fired zero times which resulted in zero rows added into MYAUDIT table.
    Seems correct to me. What else would you expect?

    [Edit: typo]

  8. #8
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by Littlefoot
    As I've said, everything seems to be OK:
    Code:
    SQL> create table myaudit
      2  (id varchar2(10),
      3   old_value number,
      4   new_value number);
    
    Table created.
    
    SQL> create or replace trigger bu_sal
      2    before update of sal on emp
      3    for each row
      4    when (new.sal < old.sal)
      5  begin
      6    insert into myaudit
      7      (id, old_value, new_value)
      8       values
      9      (:old.ename, :old.sal, :new.sal
     10      );
     11  end;
     12  /
    
    Trigger created.
    
    SQL> select sal from emp where ename = 'KING';
    
           SAL
    ----------
          5000
    
    SQL> update emp set sal = 2000 where ename = 'KING';
    
    1 row updated.
    
    SQL> select sal from emp where ename = 'KING';
    
           SAL
    ----------
          2000
    
    SQL> select * from myaudit;
    
    ID          OLD_VALUE  NEW_VALUE
    ---------- ---------- ----------
    KING             5000       2000
    
    SQL>

    well I work with toad.it gives ORA-04079: error.first time I tried with sqlplus.but when I look at with toad I couldnot see this trigger attached to employee table.then I decided to created it with toad.
    is there a syntax error here
    Thanks


    CREATE OR REPLACE TRIGGER xxx.before_employee_salary_update
    BEFORE UPDATE
    OF SALARY
    ON xxx.EMPLOYEE
    REFERENCING NEW AS new OLD AS old
    FOR EACH ROW
    WHEN (
    new.salary < old.salary
    )
    DISABLE
    BEGIN

    INSERT INTO myaudit (id, old_value, new_value)
    VALUES ( ld.id, ld.salary, :new.salary);

    END before_employee_salary_update;

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    first time I tried with sqlplus.
    Please continue running scripts/commands in sqlplus. You may easily find error details there after issuing SHOW ERRORS
    is there a syntax error here
    Yes, the DISABLE word is not allowed anywhere in the trigger creation command.
    You could find it yourself in Oracle documentation, available e.g. online on http://tahiti.oracle.com/.
    SQL Reference book, Chapters 10-19 SQL Statements, CREATE TRIGGER.

  10. #10
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by flyboy
    Please continue running scripts/commands in sqlplus. You may easily find error details there after issuing SHOW ERRORS

    Yes, the DISABLE word is not allowed anywhere in the trigger creation command.
    You could find it yourself in Oracle documentation, available e.g. online on http://tahiti.oracle.com/.
    SQL Reference book, Chapters 10-19 SQL Statements, CREATE TRIGGER.

    Hi,
    It's all about toad.Toat is putting these statements.When I do that again on sqlplus it works.
    which editor do you use? is there anybody who created trigger with toad

    Kind Regards

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    We create TRIGGERs with TOAD all the time, no problems. SQL*Plus is definitely not something we use as our normal tool for installing objects (but you better know how to use it when TOAD isn't available).

    After you removed the "DISABLE" from the CREATE or REPLACE TRIGGER script, did everything run in TOAD?

  12. #12
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by chuck_forbes
    We create TRIGGERs with TOAD all the time, no problems. SQL*Plus is definitely not something we use as our normal tool for installing objects (but you better know how to use it when TOAD isn't available).

    After you removed the "DISABLE" from the CREATE or REPLACE TRIGGER script, did everything run in TOAD?
    Yes, everything began running.It's strange because you cannot create trigger without disable/enable tag in toad.which version do you use? how do you create it?

    Thanks

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by sunsail
    you cannot create trigger without disable/enable tag in toad
    I'm not sure I properly understood what you meant to say, but - there's no ENABLE (or DISABLE) option in the CREATE TRIGGER statement; not, at least, in 10g. When the trigger is created, it is automatically enabled. If you wish, you can disable it, but you'll need to use the ALTER TRIGGER statement.

    I'd say that it doesn't really matter which TOAD version you use, but - eventually - which Oracle version you use. Besides, TOAD 8.6 creates triggers without disable / enable successfully (check the attachment), but fails if you add these options.

    In case of doubt, here's CREATE TRIGGER documentation (10g version).
    Attached Thumbnails Attached Thumbnails toad_trigger.PNG  

  14. #14
    Join Date
    Mar 2007
    Posts
    623
    the DISABLE word is not allowed anywhere in the trigger creation command.
    I have to correct myself: it is allowed in Oracle 11g, however in other place than sunsail used it, as documented in http://download.oracle.com/docs/cd/B...r.htm#i2235611. By the way, I put the link to the documentation start page before; as you did not post your Oracle version, you should search in the link to the one you use (as you can see, there are differences among Oracle versions).
    It's all about toad.Toat is putting these statements.
    If you get this statement by TOAD as SQL code of the trigger already stored in database (as I understand this statement), then TOAD generates it wrongly. Otherwise, I am not aware that TOAD or any other tool changes given SQL command.

  15. #15
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by Littlefoot
    I'm not sure I properly understood what you meant to say, but - there's no ENABLE (or DISABLE) option in the CREATE TRIGGER statement; not, at least, in 10g. When the trigger is created, it is automatically enabled. If you wish, you can disable it, but you'll need to use the ALTER TRIGGER statement.

    I'd say that it doesn't really matter which TOAD version you use, but - eventually - which Oracle version you use. Besides, TOAD 8.6 creates triggers without disable / enable successfully (check the attachment), but fails if you add these options.

    In case of doubt, here's CREATE TRIGGER documentation (10g version).
    Hi,
    Of cource I checked oracle documentation,found trigger examples oon internet.I use toad 9.6.11.you can try it.If I create trigger from scratch it puts disable row in the statement.problem is that I cannot find a way to delete it from sql.maybe that's because of I donot know how to use toad properly.
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •