September 3, 2006 at 11:56 pm
Hi I would like to find only those records who have had a particular field modifed in the row , for example , i want to be able to write query etc to find in a creditors table those records whose postcode has changed in the last month etc ...Is there an sql command or function that will enable me to trap such records ...please advise thanks
September 4, 2006 at 12:54 am
Avinash,
In current release, you cannot do this... But you can set triggers to do the job..
--Ramesh
--Ramesh
September 4, 2006 at 3:43 am
In this case it is always better to add new datetime field in the table with default value getdate()
September 4, 2006 at 4:35 am
By adding a column with def. value will only let you know when the record was inserted & not updated.. to achieve this you can add a timestamp column which will update on every insert/update action.
--Ramesh
--Ramesh
September 4, 2006 at 5:22 pm
thanks ramesh, problem is i can not modify the tables, i was hoping i could do this via code , where i can use the existing table structure ......
but with the timestamp option will it work for just one field i.e when a field is modified it will timestamp this or will it timestamp for any modifications to that record ...
please advise thanks
September 4, 2006 at 6:46 pm
Can you create triggers on the table and store audit records in Audit table? If so then that should solve your problem.
Thanks
Sreejith
September 5, 2006 at 10:17 am
Forget the timestamp column - that does not give you the date and time when the modification occurred. It is used to indicate IF a row has been modified during an update, but not WHEN.
The only way to achive what you want is to add an UPDATE trigger. You could log the changes into another (new) table from within the trigger code as was previously mentioned.
Here's an example (one of many ways to do this):
DROP TABLE LogTest
DROP TABLE LogTestAudit
GO
CREATE TABLE LogTestAudit
(
id int IDENTITY(1,1)
, modDate datetime DEFAULT GetDate() -- or CURRENT_TIMESTAMP
, modUser varchar(50) DEFAULT SUser_SName()
, col3_OldValue varchar(10)
, col3_NewValue varchar(10)
)
GO
CREATE TABLE LogTest
(
id int IDENTITY(1,1) PRIMARY KEY
, col1 int
, col2 int
, col3 varchar(10)
)
GO
CREATE TRIGGER tru_LogTest ON LogTest
FOR UPDATE
AS
INSERT LogTestAudit (col3_OldValue, col3_NewValue)
SELECT d.col3, i.col3
FROM inserted i
JOIN deleted d
ON i.id = d.id
WHERE i.col3 <> d.col3
GO
INSERT LogTest (col1, col2, col3)
SELECT 1, 1, 'AAA'
UNION ALL
SELECT 2, 1, 'BBB'
UNION ALL
SELECT 3, 1, 'CCC'
UNION ALL
SELECT 4, 1, 'DDD'
PRINT ''
PRINT 'Before any updates:'
PRINT ''
SELECT * FROM LogTest
SELECT * FROM LogTestAudit
-- Do some updates
UPDATE LogTest
SET col2 = 2 -- update col2=2 for all rows
UPDATE LogTest
SET col3 = 'ZZZZZ'
WHERE id = 2
PRINT ''
PRINT 'After updates:'
PRINT ''
SELECT * FROM LogTest
SELECT * FROM LogTestAudit
September 5, 2006 at 5:31 pm
Thanks for all your suggestions guys , i solved this yesterday using update triggers , i created an audit table and wrote a trigger in the table i wanted to monitor fields in and recorded the changes although , i had to code all possible varations since i wanted to recorded changes done once either on single fields or multipule fields in one row and so ended up writing code for field a only and field a and field b and field b only etc .....any sugestions on how i can get this code and make it smaller , i have 4 fields i am monitoring
createTrigger Creditor_changeAudit
on Creditors for update as
--only bankbsb update
if update(BankBSB) and not update(BankAccount) and not update (PaymentMethod) and not update(PaymentNotifyMethod)
begin
insert AuditlogCred (Activity)
select 'Creditors bankbsb modified by ' + ' User ' +
cast(d.CreditorsModifiedBy as varchar(50)) + ' From '+
cast(d.BankBSB as varchar (6))+ 'To ' + cast (i.BankBSB as varchar (6))
from deleted d inner join inserted i on d.ID = i.ID
end
--only bankaccount update
if not update(BankBSB) and update(BankAccount)and not update (PaymentMethod) and not update(PaymentNotifyMethod)
begin
insert AuditlogCred (Activity)
select 'Creditors bank account modified by ' + ' User ' +
cast(d.CreditorsModifiedBy as varchar(50)) + ' From '+
cast(d.BankAccount as varchar (15))+ 'To ' + cast (i.BankAccount as varchar(15))
from deleted d inner join inserted i on d.ID = i.ID
end
.......and code continues for the rest of the combination of fields ....
September 5, 2006 at 6:24 pm
Take a look at the COLUMNS_UPDATED feature of a trigger and make your Audit table look something like this...
AuditID ModDate ModBy TableName ColumnName NewValue (OldValue)
... I put OldValue in parenthesis to indicate that it's optional depending on how convenient you want to make the records. OldValue would be the same as the previous UPDATE's (separate record) new value and some balk at the idea of that level of de-normalization.
In many/most cases, such a trigger can be made very generic so you can copy it to other tables very easily. The only thing that would need to be changed would be the table name. Takes a bit of extra work but simplifies things on additional tables quite a bit.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply