Questions tagged [row-modification-time]

For questions about recording the last time, or datetime, a row was modified.

Filter by
Sorted by
Tagged with
1
vote
0answers
25 views

How a single column update is saved to disk

In learning about how data in a database is saved to disk, I came across the slide below. Earlier in the presentation they mention a block size is roughly 512 to 8,192 bytes. I haven't worked with ...
2
votes
1answer
366 views

Automating timestamp fields in PostgreSQL

I'm trying to define the following table in PostgreSQL: posts - uuid - title - created_at - updated_at - deleted_at The idea is that i never delete anything, so i change the deleted_at instead of ...
2
votes
1answer
340 views

Storing the last updated date of a row in a different table

I need to do a daily export of all of the new rows or updated rows from a legacy database into the new database. The problem I am running into is that not all of the tables on the legacy database ...
1
vote
2answers
58 views

How to set up last access (timestamptz) in table 1( basic info table) when working with many other tables having foreign key from table 1?

Let's say the database have 5 tables for sake of simplicity (there're many more in reality). Table 1 has the basic information of a user which is not changed so often. However, the other tables are ...
7
votes
1answer
6k views

PostgreSQL: Auto update updated_at with custom column [duplicate]

Before, I would join multiple tables into one and return result, but in many tables I had same column names. That's why I decided to to prefix column names with table name. But that broke the trigger ...
4
votes
2answers
2k views

Create a trigger on all the last_modified columns in PostgreSQL

In PostgreSQL 9.5, I have tables with columns in the form prefix_last_modified timestamp without time zone NOT NULL DEFAULT (clock_timestamp() AT TIME ZONE 'UTC') I was looking for a way to set ...
2
votes
1answer
3k views

Trigger to update a column in a new record to CURRENT_TIMESTAMP as it is inserted

Ok, so I'm trying to update a column to a CURRENT_TIMESTAMP in a specific record whenever the record is originally inserted. This is what I've got but otherwise I am lost. CREATE TRIGGER dbo....
5
votes
2answers
9k views

how to update column on same row with trigger without recursion?

SQL Server 2012. I need to update column [LastUpdated] with the current date and time whenever a record changes in my table. Currently I have: CREATE TRIGGER Trig_LastUpdated ON Contact AFTER ...
2
votes
1answer
140 views

SQL current timestamp

I have made a featureclass for a utility company where they can register measures set up to mitigate problems with rats, called "mitigation measure". From this feature class it is possible to state if ...
6
votes
2answers
30k views

How to reference only affected rows in AFTER UPDATE trigger

I have this table: And I'm trying to create an update trigger which will update last_updated_on = GETDATE() and last_updated_by = SYSTEM_USER columns whenever an update is performed on step_number or ...
0
votes
1answer
1k views

Whether to use or not to use: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

In one Database table, created_at & updated_at are defined as: `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP `updated_at` timestamp NOT NULL DEFAULT '0000-...
2
votes
1answer
9k views

How to create trigger to update timestamp when record is updated or inserted in another table

I have a table t_task and a table t_taskaction with a foreign key to t_task. I would like to update timestamp from t_task that is refering the foreign key form t_taskaction with every update in ...
2
votes
1answer
10k views

ADD Column ON UPDATE TIMESTAMP

I want to alter a table to include modified and created columns. However, I am having trouble with adding the modified column. I get the following error: SQL Error: ORA-00907: missing right ...
1
vote
1answer
17k views

mysql trigger to insert/update date column from datetime column

probably simple question but can't get it to work: I have a table with two datetime columns. In order to set up a date index and have simpler queries, i have added two date columns next to them. Now,...
2
votes
1answer
4k views

Export just rows modified in last month (date defined by user)

I'm asking if it's possible in MySQL to export just rows modified in a period of time for example in the past month, if it is possible, how?
5
votes
2answers
34k views

Get the Time of Last update of a column

This command gives the date of the last update for a table SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'MyDB' AND TABLE_NAME = 'MyTable' But I want to find the time of ...
5
votes
1answer
6k views

MySQL 5.5.21 ON UPDATE CURRENT_TIMESTAMP incorrect

As the title says, I'm having a few issues with 'ON UPDATE CURRENT_TIMESTAMP'. Here is the SQL definition for the table: CREATE TABLE `judgements` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `...
10
votes
1answer
45k views

How to create a trigger that update the date field and time field upon modify of the row?

I have created a table testtable inside the database testbase that have the following structure: product_no (int, not null) product_name (varchar(30), not null) price (money, null) expire_date (date, ...
13
votes
2answers
35k views

Trigger to UPDATE after UPDATE?

I want to make a trigger to record the time of any update as: CREATE TRIGGER col_update AFTER UPDATE ON col FOR EACH ROW BEGIN UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id END The ...