UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Last Updated Field, SQL Server 2008 R2    
 
   
youngb
post Jan 8 2019, 12:05 PM
Post#1



Posts: 562
Joined: 2-September 03
From: Galway, Ireland


Hi,

is there field in SQL Server that records when the records was last updated,
to save me creating my own and populating it everytime the record is update.

Thanks in Advance for any helpful suggestions.

Brian
Go to the top of the page
 
Minty
post Jan 8 2019, 12:13 PM
Post#2



Posts: 183
Joined: 5-July 16



You can turn on change tracking in SQL server, that provides complete auditing, but that might not be what you are after?

On the Server it's overhead is minimal, but to write your own for a specific table is a bit of a pain.
Go to the top of the page
 
MadPiet
post Jan 8 2019, 12:26 PM
Post#3



Posts: 2,713
Joined: 27-February 09



You could do it with an UPDATE trigger, but <shudder>.
Go to the top of the page
 
Minty
post Jan 8 2019, 12:30 PM
Post#4



Posts: 183
Joined: 5-July 16



QUOTE
You could do it with an UPDATE trigger, but <shudder>.


I did think of mentioning that , then stepped away from the vehicle ....
Go to the top of the page
 
MadPiet
post Jan 8 2019, 12:33 PM
Post#5



Posts: 2,713
Joined: 27-February 09



True. Best off to do it in the stored procedure where the update is happening. Triggers are better for when you want something to always happen in response to an insert/update/delete. Probably overkill for this.
Go to the top of the page
 
youngb
post Jan 8 2019, 12:35 PM
Post#6



Posts: 562
Joined: 2-September 03
From: Galway, Ireland


Hi Guys,

I don't need the full audit and I am concerned about the overhead but it would be usefull to know how to switch it on if available in the express version,
I would be inclined to stay away from the 'Trigger' approach, I don't mind doing my own but is there not a 'Column' of certain type that will record that last modified data/time of a record.

Thanks for the Response.

Brian
Go to the top of the page
 
MadPiet
post Jan 8 2019, 12:44 PM
Post#7



Posts: 2,713
Joined: 27-February 09



As far as I know, it's not automatic. You'd have to add it to your stored procedures. I searched around and didn't find anything.

This is for the last time a *table* was updated, not an individual record in the table:
https://stackoverflow.com/questions/6344413...e-last-modified

If you're doing all your updates through stored procedures, it would be easy enough to add that to the stored procedure.

DECLARE @TimeStamp DATETIME = GETDATE();

UPDATE MyTable
SET MyColumn = @SomeValue
, LastUpdated = @TimeStamp
WHERE.... <because nobody runs an update statement without a WHERE clause!>

… oh the "switch it on and off... The only way I can think of doing it is to store the ON/OFF value as a BIT in some table. (Say, TableName or ObjectID and OnOff BIT)

Then you would
1. get the value from the "settings" table,
2.
IF (@OnOff = True)
BEGIN
UPDATE MyTable
SET MyColumn = @SomeValue
,LastUpdated = @TimeStamp
WHERE...
ELSE
UPDATE MyTable
SET MyColumn = @SomeValue
WHERE
END

This post has been edited by MadPiet: Jan 8 2019, 12:49 PM
Go to the top of the page
 
Minty
post Jan 8 2019, 12:46 PM
Post#8



Posts: 183
Joined: 5-July 16



How are the records being updated?
If it's an Access form then add a before update event to simply update the "LastModified" field to Now()

If it's some other method perhaps you could elaborate?
Go to the top of the page
 
youngb
post Jan 9 2019, 04:19 PM
Post#9



Posts: 562
Joined: 2-September 03
From: Galway, Ireland


Hi Guys,

thanks for the replies, I know Last Modified for tables but I need it per record, I can go ahead and do it manually, (I have done before via Update Procedure, Access Forms and VBA code) was just wondering was there was a shortcut where SqlServer done the work for me. One final question is the change tracking available in SQL Server Express 2008R2.



Regards
Brian
Go to the top of the page
 
MadPiet
post Jan 9 2019, 05:02 PM
Post#10



Posts: 2,713
Joined: 27-February 09



You'd probably get a more knowledgeable answer on StackOverflow or SQLServerCentral …
Go to the top of the page
 
Minty
post Jan 10 2019, 04:12 AM
Post#11



Posts: 183
Joined: 5-July 16



Short answer = yes - more here;
https://docs.microsoft.com/en-us/previous-v...5993(v=SQL.100)
Go to the top of the page
 
youngb
post Jan 10 2019, 09:08 AM
Post#12



Posts: 562
Joined: 2-September 03
From: Galway, Ireland


I will check that out.

Thanks
Brian
Go to the top of the page
 
MadPiet
post Jan 10 2019, 11:46 AM
Post#13



Posts: 2,713
Joined: 27-February 09



Minty,
I missed it completely. Where on that page?

The only ways I can think of accessing individual records to update a column value are
(1) using a stored procedure to do the update and adding a datetime value to that update.

DECLARE @timestamp DATETIME = GETDATE();

UPDATE MyTable
SET Mycolumn = 'new value'
, LastUpdated = @timestamp
WHERE <filter>

(2) Use a trigger
...check for inserted and deleted virtual tables, if it's an update both will be populated, add timestamp there.

(3) Use Access somehow? (Might have to check Allen Browne's website...)

Did I miss anything?
Go to the top of the page
 
Minty
post Jan 11 2019, 04:33 AM
Post#14



Posts: 183
Joined: 5-July 16



@madpiet - about half way down. Under the replication feature section.

The trigger will be the most efficient method, as it will capture the change no matter where it is made, but they are frowned upon due to being difficult to troubleshoot, as they act very "silently".
But this requirement is one of the few uses they are best suited to (imvho).
Go to the top of the page
 
MadPiet
post Jan 14 2019, 10:32 PM
Post#15



Posts: 2,713
Joined: 27-February 09



oh, sure. You could make sure it's an update etc. A trigger is ideal in some ways, as it acts in response to an insert, update, or delete, but you have to be really careful when writing them, because they can cripple an application if done incorrectly.
Go to the top of the page
 
youngb
post Jan 15 2019, 08:01 AM
Post#16



Posts: 562
Joined: 2-September 03
From: Galway, Ireland


Hi Guys,


I might try the trigger solution on a smaller application with a smaller backend and only two users to check it out,
is there a possible performance hit?

Brian
Go to the top of the page
 
Minty
post Jan 15 2019, 10:36 AM
Post#17



Posts: 183
Joined: 5-July 16



Not normally - triggers are normally very efficient to operate.
I think you can see the overhead if you run the profiler in SQL Server Manager.
Go to the top of the page
 
youngb
post Jan 18 2019, 12:05 PM
Post#18



Posts: 562
Joined: 2-September 03
From: Galway, Ireland


Thanks

I will see how it goes.

Go to the top of the page
 
MadPiet
post Jan 18 2019, 02:02 PM
Post#19



Posts: 2,713
Joined: 27-February 09



Here's a really simple example I cobbled together. My table isn't pretty... I was aiming at simple. Thanks for the challenge.

CODE
CREATE TABLE TestTrigger (
    RecordID INT IDENTITY PRIMARY KEY,
    FirstName VARCHAR(20) NOT NULL,
    MI CHAR,
    LastName VARCHAR(20) NOT NULL,
    DOB DATE NOT NULL
);
GO
ALTER TABLE TestTrigger
ADD LastModified DATETIME DEFAULT NULL;

CREATE TRIGGER trgUpdate ON TestTrigger AFTER UPDATE
AS
    SET NOCOUNT ON;
    UPDATE TestTrigger
    SET LastModified = GETDATE()
    WHERE RecordID IN (SELECT RecordID FROM inserted)

INSERT INTO TestTrigger (FirstName, MI, LastName, DOB)
VALUES ('Joe', 'R', 'Bot', '1/1/1959'),
    ('Fred', 'X', 'Mitchell', '6/3/1982');

-- see that LastModified has not been changed by the INSERT
SELECT *
FROM TestTrigger;

-- but has been changed by the UPDATE.
UPDATE TestTrigger
SET LastName = 'Botticelli'
WHERE LastName = 'Bot';
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th January 2019 - 03:54 AM