Oracle - update same table on which trigger is fired

Question

I have a table temp_table with the following columns Id number, name varchar, Password varchar, pwd_change_date timestamp

I want to capture the timestamp in pwd_change_date column only when password column is changed. So basically i want to use update statement inside the trigger to update timestamp value in pwd_change_date column for the same record.

Example When a password is changed for one user, I want to capture the timestamp value in pwd_change_date for the same record.

I tried with before insert and after insert of password on temp_table, but getting mutation error. Is it allowed in Oracle to update the Same row/table on which trigger is fired?

Answer 1

You don't need to update the table again; you can modify the data before it is inserted, with a before-insert row level trigger, e.g.:

create trigger trig_pwd_date
before insert or update on temp_table
for each row
when (old.password is null and new.password is not null or new.password != old.password)
begin
  :new.pwd_change_date := systimestamp;
end;
/

db<>fiddle demo

This used the new and old correlation names to decide if the password value has changed; and the new correlation name to assign the system time to the field in the pseudorecord, which becomes the column value when the insert completes.

Hopefully you aren't storing plain-text passwords in your table.

Answer 2

SQL> create table temp_table (password varchar2(50), pwd_change_date TIMESTAMP);

Table created.

SQL> create trigger trig_pwd_date before insert or update on temp_table for each row when (old.password is null and new.password is not null or new.password != old.password) begin :new.pwd_change_date := systimestamp; end; 2 3 4 5 6 7
8 /

Trigger created.

Answer 3

SQL> set time on 15:28:42 SQL> insert into temp_table values ('23456',sysdate);

1 row created.

15:29:01 SQL> commit;

Commit complete.

15:29:09 SQL> select * from temp_table;

PASSWORD

PWD_CHANGE_DATE

12345 21-SEP-20 03.28.02.370377 PM

23456 21-SEP-20 03.29.01.478017 PM