Results 1 to 14 of 14

Thread: Historical data

  1. #1
    Join Date
    Oct 2010
    Location
    New York Metro
    Posts
    17

    Unanswered: Historical data

    I am trying to create a trigger to move historical data to another table.

    When the primary key is changed to a new (non existing) value, I want to be able to record the values of that specific row to the other table with the original PK value.

    Would there be a way for a trigger to retain the value before the update gets applied?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >When the primary key is changed to a new (non existing) value,
    some say PK value should NEVER change.
    If you insist, then reference :OLD.PK in row level trigger
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2010
    Location
    New York Metro
    Posts
    17
    I created the trigger below

    create or replace trigger table_trigger
    after update of PK
    on TABLEONE
    for each row
    BEGIN


    insert into TABLEHISTORY (PK, y z)
    select * from TABLEONE
    where PK = ld.pk

    END;
    /


    This compiles, but throws the following error:
    TABLEONE is mutating, trigger/function may not see it.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >TABLEONE is mutating, trigger/function may not see it.
    Correct.
    do NOT do SQL against table upon which trigger is based.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    How many rows in TABLEONE have the value of PK equal to :OLD.PK? If more than one, do you really want to log all of them? If at most one (the updated row), why not simply
    Code:
    insert into TABLEHISTORY (PK, y z)
    values(:OLD.PK, :NEW.y :NEW.z);
    (suppose you want actual values from other columns, not the old ones too; otherwise use :OLD instead of :NEW)?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by dataman2010 View Post
    I am trying to create a trigger to move historical data to another table.
    Which Oracle version are you using?
    Oracle 11g can do that for you automatically.
    Checkout "flashback archive" in the manual

  7. #7
    Join Date
    Oct 2010
    Location
    New York Metro
    Posts
    17
    insert into TABLEHISTORY (PK, y z)
    values(:OLD.PK, ,y, z);

    however the compiler complains about z*, and refuses to identify "insert into TABLEHISTORY"

    ora-00984 column not allowed here.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    missing a comma, perhaps?

    please use COPY & PASTE so we can see EXACTLY what you do & how Oracle responds
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Oct 2010
    Location
    New York Metro
    Posts
    17
    create or replace trigger pk_trigger
    after update of pk
    on tableone
    for each row

    begin

    insert into tablehistory (pk, x, y, z)
    values (ld.pk, x, y, z);
    END;
    /

  10. #10
    Join Date
    Oct 2010
    Location
    New York Metro
    Posts
    17
    error messages

    SQL Statement ignored for "insert into tablehistory"

    column not allowed here for column z in values (pk, x, y, z)

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post DDL for both TABLEHISTORY & TABLEONE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Oct 2010
    Location
    New York Metro
    Posts
    17
    based on privacy concerns/restrictions i can only give the hypothetical tables.

    I have made the ddl for tableone and tablehistory identical

    create table tableone (pk number(*,0) not null enable,
    x date, y number(5,0),
    z varchar2 (3 byte))

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    1 create or replace trigger pk_trigger
    2 after update on tableone
    3 for each row
    4 declare
    5 begin
    6 insert into tablehistory (pk_id, xxx, yyy, zzz)
    7 values (ld.pk_id, ld.xxx, ld.yyy, ld.zzz);
    8* END;
    SQL> /

    Trigger created.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Oct 2010
    Location
    New York Metro
    Posts
    17
    It works! Thank you for your input!

Posting Permissions

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