Depending on your DBMS, you may be able to use a stored procedure.
However, regardless of whether you use a SP or not, you will need to
implement an update trigger in order to grab the old value as well as the
new value. When a trigger fires, it creates one or more pseudotables
depending on the type of trigger. An insert trigger creates one
pseudotable named "inserted". A delete trigger creates one pseudotable
named "deleted". An update trigger creates two pseudotables, one named
"inserted" that contains the new values and one named "deleted" that
contains the old values.
I have never used Oracle myself, but I understand that there is
some way to capture a recordset and pass it to other functions and
procedures as a parameter. If you're using Oracle, you could then use the
trigger to capture the pseudotables and pass them to a stored procedure.
However, for simplicity's sake and portability to other DBMSs, I recommend
that you place your code directly into the trigger.
When writing triggers, keep in mind that it is possible for the
trigger to fire on an empty recordset. In other words, if the insert or
update or delete actually affects zero rows, it is possible for the
trigger to fire anyway. It is a good idea to check the number of rows in
the pseudotable(s) and "short-circuit" the trigger by exiting if the row
count is zero. Also, since a trigger will fire on every appropriate
operation against the table (every insert, every update, or every delete),
this is where you want to pull out all of your design and optimization
skills. If your trigger runs too long or uses too many resources, your
performance will suffer across the entire database. Avoid using a cursor
inside a trigger whenever possible. Often, there will be another way to
do things that avoids the use of cursors.
Eric A. Laney
Systems Architect
Verizon
If nobody uses it, there's a reason.
"kellymconf" <[EMAIL PROTECTED]>
07/01/2002 12:57 PM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
Subject: Suggestions? Change Log
Ok I am not that well versed on Stored Procedures yet. Let me try to
explain
and see if anyone
has any suggestions. I have a table of members, with all their address
info
etc. THe member may
come to the web page and change whatever fields they wish. The database
is
then updated.
Now what i would like to then do is write to another table Change_Log each
field that was change
it's OLD value and it's NEW value.
Now obviously if they are doing this via a form (using Cold Fusion) even
if a
field doesn't change
it will be submitted in the update statement.
Is there an easy way to write stored procedure that will then check what
fields were chagned
and grab the old value, the new value and the field name and insert that
into
the change_log table.
I know it can be done just don't know how to go about doing it. Any
suggestions or advice would
be appreciated.
Thanks
Kelly Matthews
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
