That’s cool

while creating audit trigger for a table with multiple columns if i need to insert column name which is updated into audit table, how can u do that?

after execution of trigger CREATE  or REPLACE  TRIGGER  superheroes_audit
 BEFORE  INSERT  OR DELETE  OR UPDATE  ON  superheroes
 FOR EACH ROW
 ENABLE
 DECLARE
  v_user  varchar2(30);
  v_date  varchar2(30);
 BEGIN
  SELECT user, TO_CHAR(sysdate, ‘DD/MON/YYYY HH24:MI:SS’) INTO v_user, v_date FROM dual;

  IF INSERTING THEN
   INSERT INTO sh_audit (new_name,old_name, user_name, entry_date, operation)
   VALUES(:NEW.SH_NAME, Null , v_user, v_date, ‘Insert’);

  ELSIF DELETING THEN
   INSERT INTO sh_audit (new_name,old_name, user_name, entry_date, operation)
   VALUES(NULL,:OLD.SH_NAME, v_user, v_date, ‘Delete’);

  ELSIF UPDATING THEN
   INSERT INTO sh_audit (new_name,old_name, user_name, entry_date, operation)
   VALUES(:NEW.SH_NAME, :OLD.SH_NAME, v_user, v_date,’Update’);
  END IF;
 END;

I am getting following error :

Error starting at line : 7 in command –
v_date  varchar2(30)
Error report –
Unknown Command

SP2-0552: Bind Variable “NEW” is not declared.

PL/SQL procedure successfully completed.

WHat was the “:NEW”, “:OLD”. I tried executing this code and it wanted binds or something.

can you please give us a video on table audit trigger
about the table having multiple columns

This worked for me. Excellent!

helpful af

thank you very much manish from rebelion rider

i have a doubt in select user line….what does the dual mean?????????????

why are u using here superheroes table instead of sh_audit table?

hello, sir I am getting this following error while compiling script of complex trigger for auditing.
Error report:
Unknown Command
Bind Variable “NEW” is NOT DECLARED
anonymous block completed

Thanks for the tutorials manish, are you planing on doing one on FORMS and REPORTS, from installation and everything, if you do ill be very interested

Why are we using “BEFORE” on DML when we are actually checking “AFTER” the DML was executed?

sir if want to audit whole table ???
can i use :OLD.table_name ????

or is there any other trick to audit more than one column in a table???

thanks

If I have many users on my application how can I know who the REAL user that inserted or updated the data on the table?

Your awesome man …!!!!

Below error occer when i try to process dml on a table which also has a trigger in same table
the requrement based on after dml how to handel such senario .

1 CREATE OR REPLACE TRIGGER autoinsert
2 AFTER INSERT OR UPDATE
3 ON triggertest
4 FOR EACH ROW
5 declare
6 pragma autonomous_transaction;
7 BEGIN
8 IF INSERTING THEN
9 insert into triggertest (ib,id) values(user,sysdate);
10 elsif UPDATING then
11 insert into triggertest(ub,ud) values(user,sysdate);
12 end if;
13 commit;
14* END;
SQL> /

Trigger created.

SQL> insert into triggertest(empno,ename,sal) values(1234,’JAMES’,890);
insert into triggertest(empno,ename,sal) values(1234,’JAMES’,890)
*
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at “SCOTT.AUTOINSERT”, line 5
ORA-04088: error during execution of trigger ‘SCOTT.AUTOINSERT’
ORA-06512: at “SCOTT.AUTOINSERT”, line 5
ORA-04088: error during execution of trigger ‘SCOTT.AUTOINSERT’
ORA-06512: at “SCOTT.AUTOINSERT”, line 5
ORA-04088: error during execution of trigger ‘SCOTT.AUTOINSERT’
ORA-06512: at “SCOTT.AUTOINSERT”, line 5

You’re better than my textbook and crappy college instructor combined

nice explanation but i want how debugging the pl/sql code and how to return more than one value by using ref cursor.. pls upload that related videos.. 🙂

best tutorial thankyou so much 🙂

WHAT IS A SH_NAME COLUMN?WHERE A CREATE THAT TYPE?