Questions tagged [row-modification-time]
For questions about recording the last time, or datetime, a row was modified.
19
questions
1
vote
0answers
28 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
690 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
396 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
61 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 ...
8
votes
1answer
8k 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
3k 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
11k 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
145 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
33k 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
10k 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
12k 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
18k 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,...
4
votes
1answer
5k 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?
6
votes
2answers
37k 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
37k 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 ...
