Forum Stats

  • 3,770,163 Users
  • 2,253,079 Discussions
  • 7,875,350 Comments

Discussions

I need to write a trigger before update or delete.

SN-R
SN-R Member Posts: 10 Blue Ribbon

when ever I need to edit or delete a data from table A, I need to insert the whole data's in table B which contains in table A with the user details like who modified/deleted and when modified/deleted . How can I do this?

someone please help me

thanks in advance

Tagged:
«1

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,493 Red Diamond

    As you've indicated in your subject, you can do this with a trigger.

    So, details of "who" modified or deleted depends on what "user" you're referring to. Often applications have an application level user rather than letting people log on as different database users, so it would be a little more tricky to pick up those details automatically. If it's just the database user then you can refer to the USER keyword to get it's value i.e. "select user from dual" will tell you your current sessions user. You could also pick up user details from the in-built database tables for the session, or from a system context if that is set by the application... so it all depends what you mean by "user" in your case.

    For details of when the modification/deletion took place, that's just a case of picking up sysdate and inserting that along with the other details.

    It would be helpful if you provided us with the code you've written so far for your trigger and explained what isn't working for it... of if you haven't done anything yet, just mock up some small example tables and trigger to try it and then post what you've done.

  • SN-R
    SN-R Member Posts: 10 Blue Ribbon

    Based on  SELECT sys_context('USERENV', 'OS_USER'),SYS_CONTEXT('USERENV','HOST') FROM dual;

  • BluShadow
    BluShadow Member, Moderator Posts: 41,493 Red Diamond

    Ok, so you've identified which "user" you want to record.

    So, all you need is a before update or delete trigger that take the :NEW or :OLD values (depending on whether the trigger is UPDATING or DELETING) and inserts those values, with your additional information of "user" and sysdate/systimestamp etc. to the destination table.

  • EdStevens
    EdStevens Member Posts: 28,533 Gold Crown

    someone please help me

    Show us what you've tried, and exactly where you are having difficulty. Otherwise, people will think you are trying to get us to do your job for you.

  • BeefStu
    BeefStu Member Posts: 284 Blue Ribbon

    @SN-R For each table you want to capture information for you can create a copy of your table and add the 'aud_' columns like I did below or anymore youdeem necessary.


    I would suggest placing these tables in a newly created table space, perhaps called aud_info?


    For cleanup, you can partition on 'aud_when' so after N days you can drop the PARTITIONS you don't want to keep around.


    create or replace
       trigger emp_attendance_aud_trg
        after insert or update or delete on emp_attendance 
        for each row
        declare
          l_operation varchar2(1) :=
              case when updating then 'U'
                   when deleting then 'D'
                   else 'I' end;
       begin
         if updating or inserting then
             insert into emp_attendance_audit
               (aud_who
               ,aud_when
               ,aud_operation
                ,seq_num
                ,batch_id
                ,employee_id
                ,start_date
                ,end_date
                ,week_number
                ,create_date
                ,create_user
                ,modify_date 
                ,modify_user)
             values    (sys_context('USERENV', 'OS_USER')
               ,sysdate
               ,l_operation             
              ,:new.seq_num
             ,:new.batch_id                              
             ,:new.employee_id
             ,:new.start_date
            ,:new.end_date
           ,:new.week_number
           ,:new.create_date
            ,:new.create_user
            ,:new.modify_date
           ,:new.modify_user);
         else
             insert into emp_attendance_audit
               (aud_who
               ,aud_when
               ,aud_operation
                ,seq_num
                ,batch_id 
                ,employee_id
                ,start_date
                ,end_date
                ,week_number
                ,create_date
                ,create_user
                ,modify_date
                ,modify_user)
             values  (sys_context('USERENV', 'OS_USER')
               ,sysdate
               ,l_operation              
              ,:old.seq_num
              ,:old.batch_id
              ,:old.employee_id
              ,:old.start_date
              ,:old.end_date
             ,:old.week_number
             ,:old.create_date
             ,:old.create_user
             ,:old.modify_date
            ,:old.modify_user);
         end if;
      end
    emp_attendance_aud_trg;
    


    Though this goes off on a bit of a tangent I always keep these 4 columns associated with every table  so I have information at the row level. Those columns can be easily populated with the trigger below.


    create_date DATE,
           create_user VARCHAR2(20),
            modify_date DATE,
            modify_user VARCHAR2(20)
    
    CREATE OR REPLACE TRIGGER emp_attendance_trg 
    BEFORE
    INSERT OR UPDATE
    ON emp_attendance
    FOR EACH ROW
    BEGIN
        IF INSERTING 
        THEN
           :new.create_date := 
    SYSDATE; 
           :new.create_user := sys_context('USERENV', 'OS_USER');
        END IF;
    
        IF UPDATING 
        THEN
           :new.modify_date := SYSDATE;
           :new.modify_user := sys_context('USERENV', 'OS_USER');
        END IF;
    END emp_attendance_trg;
    


    If you're putting these triggers in place on high volume tables where there is plenty of DML be careful of any performance implications as they may be firing constantly.

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Sep 8, 2021 7:00PM

    This sounds like trigger based auditing, which is a dreadful approach (large performance cost, trigger doesn't evolve with schema changes, audit won't handle schema evolution like column renaming, no retention size handling, direct path loads won't be captured etc etc). A MUCH MUCH better approach is to use Flashback Data Archive for auditing, here's an article I wrote about it.

    BluShadow
  • BeefStu
    BeefStu Member Posts: 284 Blue Ribbon

    @Paulzip nice writeup and very informative.

    Paulzip
  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy

    In article:

    from  T_PERSON   -- Cannot alias!!!
    versions between timestamp systimestamp - interval '5' minute and systimestamp
    

    Place the alias after flashback_query_clause:

    { { { ONLY (query_table_expression) | query_table_expression }
      [ flashback_query_clause ]
      [ pivot_clause | unpivot_clause | row_pattern_clause ] }
    | containers_clause
    | shards_clause
    }
    [ t_alias ]
    


  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond

    Thanks for the feedback. Yeah, I realised this a while back, just hadn't updated the blog comment.

  • SN-R
    SN-R Member Posts: 10 Blue Ribbon

    Thank you. I have written a trigger and i got the below error:-What will be the solution for it?


    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.