Help Required on a Trigger!

657470

Dear Fellows,

I am new to triggers. I am trying to write a trigger but continuously getting error messages on it when enabled.
Here is the scenario...

I have a table named 'MY_USERS' which has following structure:

Name Null? Type Default Value
----------------------------------------- -------- ---------------------------- --------------------------
USERID NOT NULL VARCHAR2(6)
PASSWORD NOT NULL VARCHAR2(10)
PWD_LASTMODIFIED_ON NOT NULL DATE SYSDATE

Now, I want that whenever someone will change password in the PASSWORD field, only the PWD_LASTMODIFIED_ON field in the same particular row, should automatically update its value.

I have written the following trigger for this:


CREATE OR REPLACE TRIGGER SMSUSER_PWD_LASTMODIFIED


AFTER UPDATE OF PASSWORD ON SMSUSER


FOR EACH ROW


BEGIN


UPDATE SMSUSER SET SMSUSER.PWD_LASTMODIFIED_ON=SYSDATE; +/*WHERE :new.PWD_LASTMODIFIED_ON <> :old.PWD_LASTMODIFIED_ON;*/+


END;

When I enable the commented WHERE clause in the trigger there is an error generated on update of PASSWORD field. Whereas, by commenting WHERE clause, when PASSWORD field is modified, PWD_LASTMODIFIED_ON field is updated in all rows.

Kindly help!

Regards

Edited by: SKAAT on Sep 1, 2008 10:58 AM

William Robertson

You don't need an UPDATE statement. Just assign

:new.pwd_last_modified_on := SYSDATE;

It should also be a <tt>BEFORE</tt> trigger.

btw you can format code with {noformat}

{noformat} tags.                                                                                                                                                                                                                                                                                                                                                                                                                                        

Etbin

I'm not sure if I understand it.
If you want only PWD_LASTMODIFIED_ON to change i.e. PASSWORD should retain the old value you must act before update setting:
:NEW.PASSWORD := :OLD.PASSWORD and :NEW.PWD_LASTMODIFIED_ON := SYSDATE and let the update happen.

Regards

Etbin

657470

No, it is not like that.

Instead, If I execute an update statement such as...

update my_users set password='newpass' where userid='12345';

As soon as the old password is replaced by the new password, the PWD_LASTMODIFIED_ON filed should automatically be updated with the current SYSDATE.

The trigger should update that PWD_LASTMODIFIED_ON field automatically.

Edited by: SKAAT on Sep 1, 2008 11:23 AM

William Robertson

You don't need an UPDATE statement. Just assign

:new.pwd_last_modified_on := SYSDATE;

It should also be a <tt>BEFORE</tt> trigger.

btw you can format code with {noformat}

{noformat} tags.                                                                                                                                                                                                                                                                                                                                                                                                                                        

657470

SUCCESS!

Thanks A Million, William!

William Robertson

At last, a forum point

Karthick2003

When you modify the value of a table by a trigger on which the trigger is created you may hit Mutating table error. This article shows ways to over come the error.

Thanks,
Karthick.