SQL & PL/SQL

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