Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [row-modification-time]

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

Often you will want meta-data pertaining to the last-time a row was modified. Most databases have developed special modules, macros, or procedures to get this effect,

19 questions
14
votes
3 answers

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 problem is that when this trigger tries to update the…
Googlebot
  • 4,551
  • 26
  • 70
  • 96
11
votes
1 answer

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, null) expire_time (time(7), null) However, how…
Jack
  • 2,557
  • 14
  • 37
  • 42
10
votes
2 answers

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 last update of a particular column of a table. I…
dardar.moh
  • 275
  • 1
  • 3
  • 12
10
votes
2 answers

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 the last modified value automatically updated at…
mat_boy
  • 327
  • 1
  • 3
  • 11
8
votes
1 answer

PostgreSQL: Auto update updated_at with custom column

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 I had to auto-update my updated_at column. This…
SeemsIndie
  • 183
  • 1
  • 1
  • 4
7
votes
2 answers

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 step_name column. I started creating it by…
Pawel85
  • 451
  • 4
  • 6
  • 10
6
votes
1 answer

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, `parent_id` bigint(20) NOT NULL DEFAULT '0', …
mbadger
  • 63
  • 1
  • 3
6
votes
2 answers

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 UPDATE AS SET NOCOUNT ON UPDATE ct SET LastUpdated =…
Zaphodb2002
  • 399
  • 2
  • 4
  • 12
5
votes
1 answer

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 parenthesis 00907. 00000 - "missing right…
John F.
  • 207
  • 2
  • 4
  • 8
5
votes
1 answer

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?
Reynier
  • 195
  • 1
  • 2
  • 9
2
votes
1 answer

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 action. It doesn't get any udpate in t_task table, and…
Joe
  • 203
  • 1
  • 5
  • 10
2
votes
1 answer

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 removing the row and use where deleted_at is null…
vinnylinux
  • 97
  • 1
  • 7
2
votes
1 answer

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 have last modified date columns and we don't want to…
Charlie Brumbaugh
  • 223
  • 2
  • 10
2
votes
1 answer

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.TrgUpnInsert ON [CNLH Security…
AArmentrout
  • 21
  • 1
  • 2
2
votes
1 answer

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 the current mitigation measure has been removed…
Kristian Birchvald Jensen
  • 131
  • 3
1
2