DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Help on Trigger

  1. #1
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Here is the situation

    I have table1 and would like to create another table_auditing. Table_audting will get updated when ever there is a UPDATE or INSERT in table1. I wrote a trigger on TABLE1 to accomplish this. But it is updating all the rows available in TABLE instead of Updating only MODIFIED or INSERTed rows. Can any one help on this. When I use FOR EACH ROW it is giving MUTATING error??
    Thanks
    Kishore Kumar

  2. #2
    Please, put the code here.
    Ramon Caballero, DBA, rcaballe@yahoo.com

  3. #3
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Here it is

    create or replace trigger Stats AFTER UPDATE or INSERT on test1
    FOR EACH ROW
    BEGIN
    INSERT into stats1
    select * from test1;
    END;

    Here it is giving mutating error
    Thanks
    Kishore Kumar

  4. #4
    I really didn't understand what are you trying to do, It seems like you want to duplicate all the records in the audit table every time a single row is modified, anyway, here is a trigger to audit deletes:
    CREATE OR REPLACE TRIGGER "C_H_AUDITDELETE" BEFORE
    DELETE
    ON "CIRCUIT_HEADER"
    FOR EACH ROW Begin
    Insert into NILACdelete
    Select :OLD.Identifier,
    :OLD.Label,
    :OLD.Phone_Number,
    :OLD.Work_Order,
    sysdate,
    Username,
    OSUser,
    Machine,
    Terminal,
    Program,
    Module,
    Logon_Time
    From V$Session
    Where audsid = userenv('sessionid');
    end;
    /

    Hope it helps.
    (The creator of this trigger must be granted select on sys.v$_session privilege)
    Ramon Caballero, DBA, rcaballe@yahoo.com

  5. #5
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    My Question is pretty simple

    For examble TABLE A contains 10 records. I want to update TABLE B with all the modified records of TABLE A or if NEW records are added.

    If out of 10 only one record is modified in TABLE A then I should see only that Modified record in TABLE B.

    I hope this clears the situation
    Thanks
    Kishore Kumar

  6. #6
    Join Date
    Oct 2000
    Posts
    123
    Your code id typical mutating error: selecting infos from the triggerred table in under for each level.
    Do this:
    create or replace trigger t_1
    after update or insert on table_name for each row
    BEGIN
    insert into table_name2
    (col1, col2,....)
    values( :new.col1, :new.col2,......);
    or :
    IF (updating) THEN
    ....
    ELSIF (inserting) THEN
    ...
    END IF;
    END;

    HTH

    Take care

  7. #7
    create or replace trigger Stats AFTER UPDATE or INSERT on test1
    FOR EACH ROW
    BEGIN
    INSERT into stats1(col1,col2) values (:new.col1,:new.col2);
    END;
    /
    This trigger must be created before test1 has any row or first create stats1 as select * from test
    Hope it helps
    If you want a copy of the table at all the time, please check the term replication.
    I would recommend you to put a column with the timestamp of the change.
    Ramon Caballero, DBA, rcaballe@yahoo.com

  8. #8
    Join Date
    Apr 2001
    Posts
    1
    his answer is pretty straightforward, too.

    your pl/sql stmt "....select * from test1...."

    will attempt to copy all rows from test1 to stat1 whenever any row changes in test1.
    if test1 consists of col1, col2
    and stat1 consists of oldcol1, oldcol2,newcol1,newcol2,thedate

    you could

    create or replace trigger test1_history
    after update on test1
    for each row
    begin
    insert into stat1 values
    (:old.col1, :old.col2, :new.col1, :new.col2, sysdate);
    end;
    /end

    you can go from there....

    --Kevin

  9. #9
    Join Date
    Aug 2000
    Posts
    194
    Kishore,

    Read about :OLD and :NEW, which will get you the right solution.

  10. #10
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Thanks for the help I will work on it let you know
    Thanks
    Kishore Kumar

Posting Permissions

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


Click Here to Expand Forum to Full Width