I need to write a trigger before update or delete.

SN-R

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

BluShadow

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

https://community.oracle.com/tech/developers/discussion/comment/16808276#Comment_16808276

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

BluShadow

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

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

@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

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.

BeefStu

@Paulzip nice writeup and very informative.

User_H3J7U

https://community.oracle.com/tech/developers/discussion/comment/16808339#Comment_16808339

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

https://community.oracle.com/tech/developers/discussion/comment/16808361#Comment_16808361

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

SN-R

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.