Trigger on insert

  • Hi ,

    I have a requirement where i have a table (EMP) with fields id, name, status

    What i want is after each insert write the data to another table(EMP_BACKUP) and update the status of EMP table to 1.

    Thanks

    Raj

  • How often is the table going to be inserted into?

    But a simple insert trigger would do the job, if the inserts are very few and far between.

    CREATE TRIGGER ..... ON .....

    FOR INSERT

    AS

    INSERT INTO .....

    SELECT .....

    FROM

    INSERTED

    UPDATE ....

    SET ... = ....

    FROM ...

    INNER JOIN

    INSERTED

    ON ... = ...

  • Hi,

    I have written a trigger

    ------------------------------------------------------------------

    create or replace

    trigger fetched_records_trigger

    after insert on EMP for each row

    DECLARE

    --v_username varchar2(10);

    BEGIN

    insert into EMP_BACKUP values (:new.id , :new.COUNT_PER_FETCH, :new.SUCCESS_COUNT, :new.FAILURE_COUNT, :new.FAILURE_DES, :new.STATUS);

    update EMP set status=1;

    END;

    Am able to insert the data into EMP_BACKUP table but cannot update the status column in EMP table to 1

    Am getting the below errror

    -------------------------------

    SQL Error: ORA-04091: table KCBUSER.FETCHED_RECORDS is mutating, trigger/function may not see it

    ORA-06512: at "KCBUSER.FETCHED_RECORDS_TRIGGER", line 6

    ORA-04088: error during execution of trigger 'KCBUSER.FETCHED_RECORDS_TRIGGER'

    04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"

    *Cause: A trigger (or a user defined plsql function that is referenced in

    this statement) attempted to look at (or modify) a table that was

    in the middle of being modified by the statement which fired it.

    *Action: Rewrite the trigger (or function) so it does not read that table.

  • Ah, an Oracle trigger, you would of been best posting that in the "Working with Oracle" section instead as this is primarily a Microsoft SQL Server forum.

    Someone with Oracle experience may find this post and help you out, but I don't know Oracle, so I would recommend reading the Oracle Documentation on creating triggers.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply