Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    12

    Unanswered: Help - Creating a log of changes

    Hey guys need help on this code. Bascailly on update or delete or the property value I need to log the changes. This is the code ive got so far:

    There is a table in the database called property where the asking price is stroed. So on update etc the trigger should be fired, but it doesnt work, it creates the trigger but with errors.

    DROP TABLE property_log;

    CREATE TABLE property_log (
    USERNAME VARCHAR2(8),
    V_DATE DATE,
    TIME VARCHAR2(10),
    PRICE NUMBER (7,2));


    drop trigger prop_log;

    create or replace trigger prop_log
    after delete or update of Agreed_asking_price
    on property
    for each row
    declare
    username varchar2(10);
    now date;
    price property.Agreed_asking_price%TYPE;
    begin

    select user, sysdate into username, now from dual;


    if updating then
    insert into property_log
    (username, v_date, time, price)
    values
    (username, now,'UPDATE',:new.price);
    end if;

    -- if deleting, then we want to keep record of old data

    if deleting then
    insert into property_log
    (username, v_date, time, price)
    values
    (username, now,'DELETE',ld.price);
    end if;


    end;

    /

    I understand im askin alot but any info would be much appricated, thanx

    Jim

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    have you heard of Oracle's AUDITING feature?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2004
    Posts
    12
    No whats that? Will it help?

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I'm not sure what your error is, but try adding the line below...
    You are referencing :new.price in the values clause of your insert ...
    What error are you getting ...

    create or replace trigger prop_log
    after delete or update of Agreed_asking_price
    on property
    REFERENCING NEW AS NEWOLD AS OLD --- Add this line ...
    for each row


    HTH
    Gregg

  5. #5
    Join Date
    Mar 2004
    Posts
    12
    Thanx for helping!

    hi i tried that, got this error

    REFERENCING NEW AS NEWOLD AS OLD
    *
    ERROR at line 4:
    ORA-04079: invalid trigger specification

    it seemed to do more than usual, i think it may be problems with the ld.price and :new.price, maybe??! Not very good at this stuff!

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by Jim:0 )
    No whats that? Will it help?
    read up on it.
    Why write you own auditing code when Oracle can do it for you and more efficiently with less overhead?

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Mar 2004
    Posts
    12
    Ahhh it works!

    I took out the ld.price and :new.price etc and just used price. It works!

    Its still not correct because I dont think it will log what the change is or used to be, if u get me, because ive taken out the old and new.

    Ah!!

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    SHOULD HAVE BEEN A "SPACE" BETWEEN NEW OLD

    REFERENCING NEW AS NEW OLD AS OLD --- Add this line ...

    This way you can reference ld.price AND :new.price and insert into
    your log table....

    If you have time, you should listen to DUCK ... Oracle does a good job with auditing ... Read up on it ...

    Gregg

  9. #9
    Join Date
    Mar 2004
    Posts
    12

    Unhappy

    Hey thanks for your help guys much appricaited.

    Unfortunatly this is university work and cant really do somthin they dont want. I could get the trigger to work, dam thing! Ill keep trying.

    Thanks again

    Jim

Posting Permissions

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