Questions tagged [row-modification-time]
For questions about recording the last time, or datetime, a row was modified.
19
questions
1
vote
0
answers
31
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 ...
- 155
2
votes
1
answer
2k
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 ...
- 95
2
votes
1
answer
583
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
2
answers
112
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 ...
- 113
8
votes
1
answer
22k
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 ...
- 183
8
votes
2
answers
8k
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 ...
- 277
2
votes
1
answer
5k
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....
- 21
6
votes
2
answers
17k
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 ...
- 389
2
votes
1
answer
164
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
2
answers
39k
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 ...
- 441
0
votes
1
answer
2k
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-...
- 181
2
votes
1
answer
14k
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 ...
- 203
4
votes
1
answer
18k
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 ...
- 197
1
vote
1
answer
21k
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,...
- 121
5
votes
1
answer
6k
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?
- 195
9
votes
2
answers
46k
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 ...
- 265
6
votes
1
answer
8k
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,
`...
- 63
10
votes
1
answer
49k
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, ...
- 2,349
14
votes
3
answers
43k
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 ...
- 4,261