What is the best way to save LastUpdatedDate column in SQL?

Question

Suppose I have a database table that has a timedate column the last time it was updated or inserted. Which would be preferable:

• You have a trigger that updates the field.
• Ask the installation / upgrade program to install the field.

The first option seems the easiest, since I don’t even need to recompile it, but it really is not. In addition, I had problems thinking about any reasons to do one over the other. Any suggestions?

Answer 1

The first option may be more reliable, since the database will support this field. This is due to the potential costs of using triggers.

If you could write other applications to this table in the future using your own interfaces, I would go with a trigger so that you don't repeat this logic anywhere else.

If your application, to a large extent, it or some other applications will access the database through the same data file, I would avoid this nightmare, which triggers can trigger and put logic directly into your datalayer (SQL, ORM, stored procs , etc.).

Of course, you have to make sure that your source of time (your application, your users, your SQL server) is accurate anyway.

---

Regarding why I don't like triggers:

Perhaps I had a rash, calling them a nightmare. Like everything else, they fit in moderately. If you use them for very simple things like this, I can get on board.

This is when trigger code becomes complex (and expensive) that triggers a lot of problems. This is a hidden tax on each insert / update / delete request you make (depending on the type of trigger). If this tax is acceptable, they may be the right tool for the job.

Answer 2

You did not mention 3. Use the stored procedure to update the table. The procedure can set timestamps as desired.

It may not be feasible for you, but I have not seen it mentioned.

Answer 3

I would say the trigger just in case that someone uses something other than your application to update the table, you probably also want to have LastUpdatedBy and use SUSER_SNAME () for this, so you can see who made the update

Answer 4

As long as I use the DBMS whose triggers I trust, I will always use the trigger parameter. This allows the DBMS to take care of as many things as possible, which is usually good.

This works, under any circumstances, make sure the timestamp column has the correct value. Overhead would be negligible.

The only thing that would be against triggers is portability. If this is not a problem, I do not think there is a question in which direction to go.

Answer 5

I am a proponent of stored procedures for everything. Your service pack may contain GETDATE () for the column.

And I don't like the triggers for such an update. Lack of trigger visibility tends to cause confusion.

Answer 6

It sounds like business logic to me ... I would be more likely to put this in code. Let the database manage data storage ... No more and no less.

Answer 7

Triggers are a blessing and a curse.

Blessing. You can use them to include all kinds of custom constraint checks and data management without any knowledge or changes to the backend systems.

Curse: you do not know what is happening behind your back. Concurrency problems / deadlocks with additional objects nested in transactions that were not originally expected. Phantom behavior, including session environment changes, untrusted strings. Excessive conditions ... additional penalties / effectiveness.

The answer to this question (update dates implicitly (trigger) or explicitly (code)) exacerbated strongly affects the context. For example, if you use the last modification date as an information field, you can change it only when the "user" really makes significant changes to the line against an automated process that simply updates some internal marker users, do not care about.

If you use a trigger to synchronize changes or you don't have control over the code that triggers the trigger, it makes sense.

My advice on the trigger is to use it to be careful. On most systems, you can filter the execution based on the operation and change the fields. The proper use of “before” and “after” triggers can have a significant impact on performance.

Finally, several systems can execute one trigger with several changes (several lines executed in a transaction), your code should be ready to apply itself as a mass update for several lines.

Answer 8

I would usually say that this is a database, but it depends on your application. If you use LINQ-to-SQL, you can simply set this field as a Timestamp and use the DAL for the Timestamp field for concurrency. It automatically processes it for you, so code repetition is not an event.

If you are writing your DAL, however, I would be more likely to do this on the database side, as it will greatly simplify the writing of user interfaces - although most likely I will do this in a stored procedure that has “public” access, and tables blocked - you don’t want the clown to go ahead and go around your stored procedure, writing directly to tables ... unless you plan to make your DAL a standalone component that any future application should use to access the database nnyh, and in this case, you can code it directly into the DAL - of course, you should do this only if you can ensure that each access to the database makes it through your component DAL.

If you are going to allow "public" access to the database for insertion into tables, you will have to go with a trigger, because otherwise, anyone can insert / update one field in the table, and the updated field may never be updated.

Answer 9

I would have the date contained in the database, i.e. trigger, stored procedure, etc. In most of your database-driven applications, a user application will not be the only means by which business users receive data. There are reporting tools, excerpts, custom SQL, etc. The corrections made by the database administrator also update that the application will also not provide a date.

But, frankly, the reason number 1, which I would not have made from the application, is the lack of control over the date / time on the client machine. They can roll it back to get more days from a trial license for something or just want to do bad things in your program.

Answer 10

You can do this without a trigger if your database supports default values ​​for fields. For example, in SQL Server 2005, I have a table with a field created as follows:

create table dbo.Repository ( ... last_updated datetime default getdate(), ... ) 

then the insert code just leaves this field from the list of insert fields.

I forgot that it worked only for the first insertion - I also have an update trigger to update the date fields and put a copy of the updated record in my history table that I would publish ... but the editor continues to exit my code ...

Finally:

 create trigger dbo.Repository_Upd on dbo.Repository instead of update as --************************************************************************** -- Trigger: Repository_Upd -- Author: Ron Savage -- Date: 09/28/2008 -- -- Description: -- This trigger sets the last_updated and updated_by fields before the update -- and puts a copy of the updated row into the Repository_History table. -- -- Modification History: -- Date Init Comment -- 10/22/2008 RS Blocked .prm files from updating the history as they -- get updated every time the cfg file is run. -- 10/21/2008 RS Updated the insert into the history table to use the -- d.last_updated field from the Repository table rather -- than getdate() to avoid micro second differences. -- 09/28/2008 RS Created. --************************************************************************** begin --*********************************************************************** -- Update the record but fill in the updated_by, updated_system and -- last_updated date with current information. --*********************************************************************** update cr set cr.filename = i.filename, cr.created_by = i.created_by, cr.created_system = i.created_system, cr.create_date = i.create_date, cr.updated_by = user, cr.updated_system = host_name(), cr.last_updated = getdate(), cr.content = i.content from Repository cr JOIN Inserted i on (i.config_id = cr.config_id); --*********************************************************************** -- Put a copy in the history table --*********************************************************************** declare @extention varchar(3); select @extention = lower(right(filename,3)) from Inserted; if (@extention <> 'prm') begin Insert into Repository_History select i.config_id, i.filename, i.created_by, i.created_system, i.create_date, user as updated_by, host_name() as updated_system, d.last_updated, d.content from Inserted i JOIN Repository d on (d.config_id = i.config_id); end end 

Ron