| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: database trigger
I think that you would still want to do this in a before update
trigger for each row. I don't think that you can assign the :new
fieldnames in an after update trigger.
-Jeff Guttadauro
On Tue, 06 Jul 1999 19:44:42 GMT, Marc Mazerolle <informaze_at_sympatico.ca> wrote:
>
>--------------09BD04406788FBEE6FF30A4F
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>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.
>
>--------------09BD04406788FBEE6FF30A4F
>Content-Type: text/html; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
><!doctype html public "-//w3c//dtd html 4.0 transitional//en">
><html>
>Do an after update trigger for each row with the following code....
><p><tt>BEGIN</tt>
><br><tt> IF (OLD.USER_ID = 'TRAINING') THEN</tt>
><br><tt> IF (OLD.USER_PASSWORD
><> NEW.USER_PASSWORD) THEN</tt>
><br><tt>
>NEW.USER_PASSWORD := OLD.USER_PASSWORD;</tt>
><br><tt> END IF;</tt>
><br><tt> /* Prevent the USER_ID
>called 'TRAINING' from becomming someone else....</tt>
><br><tt> This
>to prevent an UPDATE on USER_ID from 'TRAINING' to 'DUMMY'</tt>
><br><tt> followed
>by an UPDATE on USER_PASSWORD and then an UPDATE on</tt>
><br><tt> USER_ID
>from 'DUMMY' to 'TRAINING'....</tt><tt></tt>
><p><tt> We
>might want to prevent DELETE on USER_ID 'TRAINING' by other users... */</tt>
><br><tt> NEW.USER_ID := OLD.USER_ID;</tt>
><br><tt> END IF;</tt>
><br><tt>END;</tt>
><p>Marc
><p>Simon wrote:
><blockquote TYPE=CITE>I have a table called users with a field that holds
>an encrypted password
><br>'user_password' and a field that holds the user's name 'user_id'.
><p>This is a generic account in the associated application which needs
>it's
><br>password to remain the generic password, yet, in spite of efforts to
>the
><br>contrary, users in a training class will inevitably change the password
>and
><br>commit.
><p>I want to write a database trigger that will fail the commit when the
><br>user_id = 'TRAINING' so the password change does not occur. I
>may want to
><br>give a message and I may want it to be transparent to the user.
><p>I've done a before update trigger, but it isn't trapping the event if
>I
><br>manually try to update the user_password field through sqlplus where
><br>user_id='TRAINING'.
><p>Help please!
><br>:)
><br>Got to do this today if possible.</blockquote>
></html>
>
>--------------09BD04406788FBEE6FF30A4F--
>
Received on Tue Jul 06 1999 - 16:32:58 CDT
 |
 |