Re: database trigger

From: Marc Mazerolle <informaze_at_sympatico.ca>
Date: Tue, 06 Jul 1999 19:44:42 GMT
Message-ID: <37825E3B.24D9301A@sympatico.ca>

Do an after update trigger for each row with the following code....

BEGIN
    IF (OLD.USER_ID = 'TRAINING') THEN

        IF (OLD.USER_PASSWORD <> NEW.USER_PASSWORD) THEN
            NEW.USER_PASSWORD := OLD.USER_PASSWORD;
        END IF;
        /* Prevent the USER_ID called 'TRAINING' from becomming someone
else....
           This to prevent an UPDATE on USER_ID from 'TRAINING' to 'DUMMY'
           followed by an UPDATE on USER_PASSWORD and then an UPDATE on
           USER_ID from 'DUMMY' to 'TRAINING'....

           We might want to prevent DELETE on USER_ID 'TRAINING' by other
users... */
        NEW.USER_ID := OLD.USER_ID;

    END IF;
END; Marc

Simon wrote:

> I have a table called users with a field that holds an encrypted password
> 'user_password' and a field that holds the user's name 'user_id'.
>
> This is a generic account in the associated application which needs it's
> password to remain the generic password, yet, in spite of efforts to the
> contrary, users in a training class will inevitably change the password and
> commit.
>
> I want to write a database trigger that will fail the commit when the
> user_id = 'TRAINING' so the password change does not occur. I may want to
> give a message and I may want it to be transparent to the user.
>
> I've done a before update trigger, but it isn't trapping the event if I
> manually try to update the user_password field through sqlplus where
> user_id='TRAINING'.
>
> Help please!
> :)
> Got to do this today if possible.
Received on Tue Jul 06 1999 - 14:44:42 CDT