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 is the function I had: CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ language 'plpgsql'; And I would add a trigger like: CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_updated_at(); But now my column is named users_updated_at and this doesn't work. My question is is there any way that I can pass the column name to the trigger and update the passed column, or is there is any other way that I am not aware of?

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 updated column, it is also another update event, which runs the trigger. This will create an infinite loop, which does not work. How can I store the update time in the corresponding column? I wish to use a trigger because there are many columns in the table. If I try to set the update time manually, I would need to modify many queries.

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?

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 files much before, I basically think about programming in terms of main memory access. But the presentation says to transfer 1 block into main memory it takes about 1ms. This is opposed to nanosecond-scale main-memory modifications on individual records. So the slide below says that to modify a single record (a single column of a record even), you must save an _entire block_ back to disk. I assume this is for every single change you make. So for every network request that comes into the database calling UPDATE, that is 1 block read and 1 block write (assuming it's for one record in one table). That is ~2ms per update. If you have [100k requests per second](https://www.quora.com/How-many-requests-per-second-do-Twitter-servers-have-to-handle) , that's 100 requests per millisecond. Assuming each request did 1 record update, that means you probably have 50 databases handling the request load. I know my math and assumptions are probably wrong. Which is why I am asking this question: 1. If you really must read and write an entire block per modification. It seems like you could read just the slice you wanted instead. 2. How a database optimizes updates so you can fit more into that ~2ms window per update. I can imagine if you batch update a few records, they all are in different blocks, so that means even slower time. --- [](http://www.cs.sfu.ca/CourseCentral/454/johnwill/cmpt454_02disks.pdf)

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 each update of the rows, and I found this nice post that defined the function: CREATE OR REPLACE FUNCTION update_modified_column() RETURNS TRIGGER AS $$ BEGIN NEW.modified = now(); RETURN NEW; END; $$ language 'plpgsql'; Now, I'd like to know if there is any way to pass the column name to the PostgreSQL function and to execute it to the NEW row? E.g. CREATE OR REPLACE FUNCTION update_modified_column(varchar column) RETURNS TRIGGER AS $$ BEGIN NEW.column = now(); RETURN NEW; END; $$ language 'plpgsql';

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 break anything by adding new columns to the legacy tables.** The worry is that since there are a lot of different processes **inserting records, that the columns might not have been specified in those processes**, and since we are switching to the new schema it's not worth it to rewrite those processes yet. See this code contributed by Evan Carroll as to the problem caused by adding another column. CREATE TABLE f (a int); INSERT INTO f VALUES (1); ALTER TABLE f ADD b int; INSERT INTO f VALUES (2); This causes the error, Error Code: 1136. Column count doesn't match value count at row 1 My current idea is to to create a new table in the legacy database and then create a **trigger on the old table to insert the date and primary key of any new rows created or updated in the old tables.** Then I can export just the new/updated rows and import them into the new database. Would there be better ways of doing this?

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 or not. This field is named "Removed" and it is a domain value with possibilities "Yes", "No" and "Not set". I now wish to create a sql trigger that can update a time field called "DateRemove" with the current timestamp for the moment the utility company changes the field "Removed" from "No" to "Yes". But only when this exact change happens, not when other changes occur within the feature class.

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 Authentication].dbo.DateTimestamp AFTER INSERT, INSERT INTO [CNLH Security Authentication].dbo.EntranceInformation (DateTimestamp) Values (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-00-00 00:00:00' COMMENT 'Updated At', And in another database table, it's defined as: created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created At', updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Updated At', created_at -> stores datetime when the data is created. updated_at -> stores the datetime when data is updated. Which definition among above two would you prefer & why?

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 parenthesis" I was reading this Oracle document (http://docs.oracle.com/cd/E17952_01/refman-5.5-en/timestamp-initialization.html) to get an example on how to properly write the ON UPDATE syntax Here is my SQL. ALTER TABLE FOOBAR ADD ( created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); Why am I getting a missing right parenthesis error?

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', entry_id bigint(20) NOT NULL, group_id bigint(20) NOT NULL, comments longtext, rating int(11) DEFAULT '0', status int(11) NOT NULL DEFAULT '1', modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id,entry_id,group_id), KEY group_id (group_id), KEY entry_id (entry_id), KEY status_id (status), CONSTRAINT entry_id FOREIGN KEY (entry_id) REFERENCES entries (id), CONSTRAINT group_id FOREIGN KEY (group_id) REFERENCES groups (id), CONSTRAINT status_id FOREIGN KEY (status) REFERENCES status (id) ) ENGINE=InnoDB AUTO_INCREMENT=9082 DEFAULT CHARSET=utf8; Records are inserted using the Wordpress database class ( $wpdb->insert ). All fields except id and modified are passed in an associative array to the function. This works as expected, the record is inserted and a timestamp is applied. The problem is that this timestamp is not always correct. In seemingly random instances (around 15% of inserts) the timestamp will be anywhere up to 30 minutes in the future... I couldn't find any sort of pattern to this behaviour which makes the problem difficult to track down. If it is relevant, this table experiences quite a high volume of inserts for several hours a day. After searching Google and this site I am no closer to tracking down the problem. I did manage to find one forum post, from several years ago, where someone had a similar problem but the only reply was to file a bug report. Has anyone else experienced this or have a theory as to why it is happening? Thanks.

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 for normal behavior. Is there a way of automating those timestamps?

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 = GETDATE() FROM Contact ct INNER JOIN Inserted i ON ct.IDContact = i.IDContact But this is recursive and I don't want that, causes deadlocks and other weirdness. I cannot turn off recursive triggers globally. I see that INSTEAD OF triggers are non-recursive, but if I do that do I have to check every other column in the Inserted to see if it got updated or will SQL Server handle that for me? What's the best way to do this?

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 ALTER TRIGGER tr_app_bread_crumbs_afterupdate ON [dbo].[app_bread_crumbs] AFTER UPDATE AS BEGIN UPDATE [dbo].[app_bread_crumbs] SET last_updated_by = SYSTEM_USER, last_updated_on = GETDATE() END But this will update all rows at once. I'm trying to figure out how to specify to only update last_updated_on and last_updated_by by the trigger for the specific row where updates are being made. For example if they update: UPDATE [dbo].[app_bread_crumbs] SET step_name = 'DAMAGE' WHERE step_number = 1 Only first row should be updated by the trigger

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 accessed very often. All these other tables have the user-id as the foreign key. I need to make a column lastAccess in table 1 such that whenever the user works with any of the table, user's lastAccess in table 1 is updated. I'm working with [python-socketio] (https://github.com/miguelgrinberg/python-socketio) server connected to PostgreSQL database using [python-psycopg2](http://initd.org/psycopg/) connector. It seems like a task which every person working with databases should know. I am new to working with RDBMS and exactly find a way to do it. A general direction, if a solution is not possible, will also be really appreciated.

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 do I write a trigger such that it will update that particular row that has been modified (or updated with new information) and record the modification date into the expire_date field and the modification time into the expire_time field? (or if this is possible?)

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 can't use triggers because I want to know the time of last update of a specific column from a system table. I hope I explained well my problem.

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, i would like to, whenever a row is inserted or updated, to extract the date part from the datetime columns, and insert or update the date columns. Not very well versed with triggers, so pointers would be much appreciated. server version: 5.1.49-3

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 I'm not getting any error. USE myTask; DELIMITER $$ CREATE TRIGGER t_taskaction_AUPD AFTER UPDATE ON t_taskaction FOR EACH ROW BEGIN DECLARE idTask INT(11); DECLARE idTaskState INT(11); set @idTask = NEW.idTask; set @idTask = (SELECT idTaskState FROM t_task WHERE idTask = @idTask); UPDATE t_task SET idTaskState = @idTaskState WHERE idTask = @idTask; END$$ DELIMITER ; **Tables** CREATE TABLE IF NOT EXISTS myTask.t_task ( idTask INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, task VARCHAR(100) NOT NULL, `idTaskState' INT(11) UNSIGNED NOT NULL DEFAULT 1, description LONGTEXT NOT NULL, timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (idTask) ) CREATE TABLE IF NOT EXISTS myTask.t_taskAction ( idTaskAction INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, idTask INT(11) NOT NULL, fecha DATETIME NOT NULL, action VARCHAR(45) NOT NULL, timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (idTaskAction, idTask), CONSTRAINT t_ifocTareaAccion2t_ifocTarea FOREIGN KEY (idTaskAction) REFERENCES myTask.t_task (idTask) ON DELETE CASCADE ON UPDATE CASCADE) CREATE TABLE IF NOT EXISTS myTask.a_taskState ( idTaskState INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, order INT(11) NULL DEFAULT NULL, state VARCHAR(45) NOT NULL, timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (idTaskState))