|
|
Posted by rdraider on 07/03/25 11:52
Hi all,
We have an app that uses SQL 2000. I am trying to track when a code field
(selcode) is changed on an order which then causes a status field (status)
to change. I tried a trigger but the app may use 2 different update
statements to change these fields depending on what the user does. When the
trigger fires (on update to selcode), the status field has already been
changed. So my trigger to record the changes from inserted and deleted do
not get the true 'before' value of the status field.
The app does use a log table that tracks these changes. The problem I am
having is that 2 records are created, one for the change to selcode and
another for the change to status.
I am looking for help with a script to combine the existence of these 2 log
records into 1 unique record or occurance that I can track.
example:
ordlog: table that logs order changes
ordernr: order number
varname: name of field being changed
old_value: contents of field before change
new_value: contents of field after change
syscreated: date/time of log entry
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'selcode' and ordernr = '10580'
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'status' and ordernr = '10580' and old_value = 'A' and
new_value = 'O'
So I need a way to combine these 2 log entries into a unique occurance. The
ordernr and syscreated could be used to link records. syscreated always
appears to be the same for the 2 log entries down to the second. Selcode
can change from NULL to a number of different values or back to NULL.Status
is either 'A' for approved or 'O' for open. An order can have many log
entries during its life. The selcode may be changed several times for the
same order.
Ideally, I would like a result that links 2 log entries and shows the status
changed from 'A' to 'O' when selcode changed.
Thanks for your time.
Navigation:
[Reply to this message]
|