:NEW cannot be used in After Delete Trigger ?

Rakin

Hi,

Is there any way to get the :NW.value in the After delete trigger for each row. My requirement is audit log of the end user DML operations along with user Name (HERE THE USER IS NOT THE ORACLE USER, BECAUSE OF THE LARGE NUMBER OF END USERS WE ARE MAINTAINING ONE TABLE TO CREATE USER NAME & PASSWORD, WHEN THE USER LOGIN TO ORACLE FORM SCREEN, ASSIGN THE USER NAME TO GLOBAL VARIABLE) & Action Date.

Here is my code for trigger - It is working fine with INSER & UPDATE but for DELETE User is NULL

CREATE OR REPLACE TRIGGER Tgr_stud_det
AFTER INSERT OR UPDATE OR DELETE ON student_details
FOR EACH ROW
DECLARE
BEGIN
IF Inserting THEN
-------------INSERT VALUE---------------
INSERT INTO Log_student_details
(Seq,
App_User,
Action,
Action_Date,
stud_name,
stud_age,
stud_sex)
VALUES
(stud_sequence.NEXTVAL,
:NEW.App_User,
'INSERT',
SYSDATE,
:NEW.stud_name,
:NEW.stud_age,
:NEW.stud_sex);

-------------DELETE VALUE---------------
ELSIF Deleting THEN

INSERT INTO Log_student_details
(Seq,
App_User,
Action,
Action_Date,
Comment_Up,
stud_name,
stud_age,
stud_sex)
VALUES
(stud_sequence.NEXTVAL,
:OLD.App_User,
'DELETE',
SYSDATE,
NULL,
:OLD.stud_name,
:OLD.stud_age,
:OLD.stud_sex);
ELSIF Updating THEN

-------------UPDATE VALUE---------------
INSERT INTO Log_student_details
(Seq,
App_User,
Action,
Action_Date,
Comment_Up,
stud_name,
stud_age,
stud_sex)
VALUES
(stud_sequence.NEXTVAL,
:NEW.App_User,
'UPDATE',
SYSDATE,
'NEW VALUE',
:NEW.stud_name,
:NEW.stud_age,
:NEW.stud_sex);

INSERT INTO Log_student_details
(Seq,
App_User,
Action,
Action_Date,
Comment_Up,
stud_name,
stud_age,
stud_sex)
VALUES
(stud_sequence.CURRVAL,
:NEW.App_User,
'UPDATE',
SYSDATE,
'OLD VALUE',
:OLD.stud_name,
:OLD.stud_age,
:OLD.stud_sex);

END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;

END Tgr_stud_det;


Thanks in advance.

Twinkle

Hi,

I dont know much about Oracle Forms. As you mentioned "I assigned the Application User name in the Form global variable", can you put the value of the Form Global variable to a package global variable in the pre-form trigger of the Oracle Form. Then, in the after delete trigger instead of putting :new.user_name you can just assign package.pck_global_user_name. The package global variable will get the username value when the form is loaded i.e. when a user successfuly logs in i.e in pre-form trigger of Oracle Forms. It will remain throughout the session.


Hope it helps,
Twinkle

Twinkle

Hi,

Trigger fired on delete statement has reference to old values only. Since the new row is deleted there is no meaning in accessing new value and hence :new. will always return you null.

Can you share with us how and where are you assigning USER NAME value to global variable.


Twinkle

Rajesh C

Faisal,

Think about it .....

For an update, you are changing the value from ..say X to Y... So you have old and new values...

For an insert... you are adding a value... all you have is the :new value (the old values has no meaning logically...there is no old record at all..)

For a delete, you are deleting the existing value (the old record...). There is no :new record.( Logically it has no meaning either..)

Even in the case where you have to log dml statements....

When a record is deleted, you have to log the values of the deleted record, so you only need the "OLD" values.

Since you have the user name in a global variable let's say... g_user_id, you should directly use that value in the insert statement.

The :OLD and :NEW values are only when you are trying to reference the values from the record.
So in your case, assuming the variable name is pkg_test.USER_ID which is populated after he logs in by means of a trigger.. the insert into log table would simply have....

INSERT INTO Log_student_details
(Seq,
App_User,
Action,
Action_Date,
Comment_Up,
stud_name,
stud_age,
stud_***)
VALUES
(stud_sequence.NEXTVAL,
pkg_test.App_user, ---- This is the global variable that you populate 
'DELETE',
SYSDATE,
NULL,
:OLD.stud_name,
:OLD.stud_age,
:OLD.stud_***);

Rakin

Hi Twinkle & Rajesh

Sorry I think I didnt give the complete requirements, these values are coming Oracle Form Interface. In the form only I assigned the Application User name in the Form global variable. Since as I mentioned before, the user are not the oracle users, these users are defined in a table. and will be verified upon login to application by using custom login screen.

Also we need the user name in Delete action also, For eg: User A define a student S1 and User B login to the application and deleted the entry of Student S1, so in the log table there should be two enty to say User A defined student S1 on this date & time, and User B deleted student S1 on this date & time, Hope you understood the reuirements.

Thanks again.

Twinkle

Hi,

I dont know much about Oracle Forms. As you mentioned "I assigned the Application User name in the Form global variable", can you put the value of the Form Global variable to a package global variable in the pre-form trigger of the Oracle Form. Then, in the after delete trigger instead of putting :new.user_name you can just assign package.pck_global_user_name. The package global variable will get the username value when the form is loaded i.e. when a user successfuly logs in i.e in pre-form trigger of Oracle Forms. It will remain throughout the session.


Hope it helps,
Twinkle

743848

Hi,

:new can use with only insert and update trigger
:old can used with only delete and update trigger

Rajesh C

Rizly,

As i mentioned in the above post, you should remove the references of :old and :new when you are trying to use the global variables. These values are only significant when you the talk about the record in the table.

For the scenario, you explained, your trigger would insert two records....The trigger would be fired twice.. once during the insert and once during the delete. The audit table will have two records indicating both the actions..

Take a look at this example below...I am artificially manufacturing a user id in the package test_pkg and using that in the insert trigger. As i explained above, you dont need the :old and :new references because the user id is not a column in the table . hence the :old and :new references have no relevance.

Also note that, for the delete, I use the :old value and for the insert, I use the :new value.
for update, I assume you want to store the old record and hence used :old (you can of course use :new too..technically.).
I don't have access to a forms environement, but the user id logic should be similar to what I described below.

sql> create table t(
  2     id number,
  3     name varchar2(20)
  4  );

Table created.

sql> create table t_audit
  2     ( id number,
  3       name varchar2(20),
  4       action varchar2(20),
  5       user_id varchar2(20)
  6  );

Table created.

sql> create or replace package test_pkg as
  2      function get_user_id return varchar2;
  3  end test_pkg;
  4  /

Package created.

sql> create or replace package body test_pkg as
  2      function get_user_id return varchar2 is
  3      begin
  4          return 'USER' || to_char(sysdate,'HH24:MI');
  5      end get_user_id;
  6  end test_pkg;
  7  /

Package body created.

  1  create or replace trigger trg_biud_t
  2     before insert or update or delete on t
  3     for each row
  4  begin
  5     if INSERTING then
  6        insert into t_audit values (:new.id, :new.name, 'INSERT',test_pkg.get_user_i
  7     elsif UPDATING then
  8        insert into t_audit values (:old.id, :old.name, 'UPDATE',test_pkg.get_user_i
  9     elsif DELETING then
 10        insert into t_audit values (:old.id, :old.name, 'DELETE',test_pkg.get_user_i
 11     end if;
 12* end;
sql> /

Trigger created.

sql> select * from t;

no rows selected

sql> select * from t_audit;

no rows selected

sql> insert into t values (100, 'Rajesh');

1 row created.

sql> insert into t values (200,'Kumar');

1 row created.

sql> delete from t where id = 200;

1 row deleted.

sql> commit;

Commit complete.

sql> select * from t
  2  /

        ID NAME
---------- --------------------
       100 Rajesh

sql> select * from t_audit;

        ID NAME                 ACTION               USER_ID
---------- -------------------- -------------------- --------------------
       100 Rajesh               INSERT               USER15:36
       200 Kumar                INSERT               USER15:36
       200 Kumar                DELETE               USER15:37