Re: Clarification on after triggers
Date: 1997/05/07
Message-ID: <3370d150.13695473_at_newshost>#1/1
On Wed, 07 May 1997 12:34:18 -0600, krichine_at_radss.awp.com wrote:
>Hi!
>
>I would like to do some bookkeeping for some of the tables in the
>database. There are two columns in the tables that I use it for:
>username and timestamp.
>I want the values in these columns to contain the user who inserted or
>updated this particular record.
>
>A year ago I got help from tkyte_at_us.oracle.com
>> create trigger t_trigger
>> before update of T
>> for each row
>> begin
>> :new.username := USER;
>> :new.timestamp := SYSDATE;
>> end;
>> /
>>
>> To secure this you could do one of a couple of things:
>> 1.) turn the above trigger into a "before insert or update", then no
>> matter what the user inserts in the username/timestamp columns or
>> updates them to be, your trigger will set them to what they should be
>> (the user can try as hard as they want to fake out the system but their
>> updates will always disappear).
>
>So far as I understand, the author meant "after insert or update".
>
No, I really meant BEFORE insert/update. Only a before trigger may modify the values.
The order of execution would always be:
User issues Update/Insert
BEFORE trigger(s) fire in some order
BEFORE, FOR EACH row trigger(s) fire in some order
AFTER, FOR EACH row trigger(s) fire in some order
AFTER trigger(s) fire in some order
No matter what the user updated username/timestamp to in the insert/update statement, your BEFORE, FOR EACH row trigger would set it to USER and SYSDATE - overriding what they put in.
Only another BEFORE, FOR EACH row trigger would be able to modify the values of these columns. Presumably, you have control over who can and cannot create triggers on the table in question.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed May 07 1997 - 00:00:00 CEST