Updating a column automatically, when another column is updated in a table

Question

Table Name:User_table

Uname       pwd          expire_date          update_pwd_date
-------------------------------------------------------------
aaa         aaa          30-AUG-2013           null
bbb         bbb          01-SEP-2013           null
ccc         ccc          15-sep-2013           null
ddd         ddd          29-SEP-2013           null

So when we are going to update pwd column, it should automatically update update_pwd_date column with current date(sysdate).

I want output like:

Uname       pwd          expire_date          update_pwd_date
-------------------------------------------------------------
aaa         eee          30-AUG-2013           23-AUG-2013
bbb         fff          01-SEP-2013           23-AUG-2013
ccc         ggg          15-sep-2013           23-AUG-2013
ddd         hhh          29-SEP-2013           23-AUG-2013

Actually i am trying to use below trigger.But it's not working.

create or replace trigger user_trg
before update of pwd
on user_tab
for each row
declare
Pragma autonomous_transaction;
begin
update user_tab set pas_update=sys date where pwd=:new.pwd;
commit;
end;

Actually i am trying to below trigger.But it's not working.

create or replace trigger user_trg before update of pwd on user_tab for each row declare Pragma autonomous_transaction; begin update user_tab set pas_update=sys date where pwd=:new.pwd`; commit; end;

Please anybody can give solution for this query?

oraclenerd oraclenerd · Accepted Answer · 2013-08-24T03:34:10

I think the other answers will give you what you need for the trigger.

I dislike triggers. A lot. Not that they don't work, it's that they are often overlooked or dependencies forgotten about. Shorter, they are the devil.

Maybe I can convince you to try it a different way.

You're already writing PL/SQL, why not just create an API (package)? It's pretty simple actually.

create or replace
package maintain_user_table
as
  procedure update_user_pwd( p_uname in varchar2, p_new_pwd in varchar2 );
end;
/

Assumptions: 1. uname is a PK

create or replace
package body maintain_user_table
as
  procedure update_user_pwd( p_uname in varchar2, p_new_pwd in varchar2 )
  is
  begin
    --error checking can go here
    update user_table
      set pwd = p_new_pwd
        update_pwd_date = SYSDATE
    where uname = p_uname;

    if sql%rowcount = 1 then
      --update successful
    else if sql%rowcount = 0 then
      --no rows found
    else
      --more than one row updated, not good, probably rollback or something
    end if;
  end update_user_pwd;
end;
/

This will allow you to more tightly control the quality of your data. Leaving a particular table open to UPDATE/INSERT/DELETE from anything but this procedure means you are just hoping that everyone follows this particular rule (which is why the trigger is necessary). Remove the trigger. Close down access to this table and force people/applications to use the PL/SQL API.

Just something to consider...

Updating a column automatically, when another column is updated in a table

3 Answers