17

Use a BEFORE trigger instead, and set the updated column assigning a value to NEW.updated (this would be a simple assignment, not an UPDATE). This way you won't trigger additional UPDATEs. Your trigger body will simply look like SET NEW.updated = NOW() I usually use AFTER triggers only for modifying other tables, BEFORE for modifying the new (or updated) ...


10

Use the inserted table, which is a special table available inside triggers containing the rows that will be updated/inserted into the table. 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() FROM dbo....


8

It would be fairly easy but I would actually recommend changing the logic of the command that is inserting/updating the data so that it adds the additional information at this point. However, should you wish to proceed with a trigger you could do something like this: create trigger my_trigger on my_table after insert, update as begin declare @dt datetime =...


6

spi module: moddatetime moddatetime — Functions for Tracking Last Modification Time moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table. To use, create a BEFORE UPDATE trigger using this function. Specify a single trigger ...


6

Your trigger needs to leverage the 'virtual inserted' table as in the following example: CREATE TRIGGER ModDate ON TableX AFTER INSERT AS BEGIN UPDATE X SET ModifiedDate = CURRENT_TIMESTAMP FROM TableX X JOIN inserted i ON X.key = i.key -- change to whatever key identifies -- the tuples END AS ...


6

That is the MySQL documentation, not the Oracle Database documentation. There is no such clause in Oracle Datababase. Use a trigger. Here is an example: link


5

You need to use the INSERTEDand DELETED pseudo-tables. One way to do this: ALTER TRIGGER tr_app_bread_crumbs_afterupdate ON [dbo].[app_bread_crumbs] AFTER UPDATE AS BEGIN UPDATE upd SET last_updated_by = SYSTEM_USER, last_updated_on = GETDATE() FROM [dbo].[app_bread_crumbs] AS upd JOIN Inserted AS i ON i.id = upd.id ...


5

Use the spi module's moddatetime spi module, moddatetime moddatetime — Functions for Tracking Last Modification Time moddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table. To use, create a BEFORE UPDATE trigger using this function....


5

None of the system tables (that is, nothing in the INFORMATION_SCHEMA database) exist that has that kind of information recorded anywhere. In other words, there is no native mechanism to put any timestamps on column changes. Any time that one or more columns change in any row a new row is added an old is deleted an ALTER TABLE of any kind the UPDATE_TIME ...


3

Given that you cannot disable recursive triggers, the next best options are: Have the trigger detect how many levels deep it is using TRIGGER_NESTLEVEL function. Use this at the beginning of the trigger to simply exit if it is not the 1st trigger execution in the stack. Something along the lines of: IF (TRIGGER_NESTLEVEL() > 1) BEGIN -- Uncomment the ...


3

I don't think you are supposed to use DEFAULT (constant) with ON UPDATE. According to the MySQL Docuementation With an ON UPDATE CURRENT_TIMESTAMP clause and a constant DEFAULT clause, the column is automatically updated to the current timestamp and has the given constant default value. CREATE TABLE t1 ( ts TIMESTAMP DEFAULT 0 ON UPDATE ...


3

This would depend on the table having a timestamp column. mysqldump has a --where option that you can define when dumping a specific table EXAMPLE If you have a table called mydb.mytab defined as follows CREATE TABLE mytable ( id INT NOT NULL AUTO_INCREMENT, tm TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) )...


2

It's unclear why you need this but assuming that the table looks something like CREATE TABLE Table1 (`id` int not null auto_increment primary key, `dt1` datetime, `dt2` datetime, `d1` date, `d2` date); your triggers might look the following way CREATE TRIGGER tg_bi_table1 BEFORE INSERT ON table1 FOR EACH ROW SET NEW.d1 = ...


2

What you could is change your table so it stores the timestamp whenever it is updated. For example CREATE TABLE foo ( id INT PRIMARY KEY x INT, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY (updated_at) ); The above code store the current timestamp to a new row and changes it the time edited ...


2

Thanks Michael for your help! Good to know that @variables are users variables and without @ are local variables. The following snipped worked well USE `myTask`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` TRIGGER `t_taskaction_AUPD` AFTER UPDATE ON `t_taskaction` FOR EACH ROW BEGIN DECLARE _idTask INT(11); DECLARE _idTaskState INT(11); ...


2

Different columns, created_at this simple, just give it a DEFAULT now() updated_at use the spi module which provides a higher-order trigger than can be applied to any table, see this post, also see row-modification-time deleted_at how would you automated that anyway? What would that even look like? Just set it when you delete the post.


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. Your method will work, but that seems almost more likely to break something.. In that case, you're talking about doubling your write load. In the other case you'...


1

I have two solutions: Create a lastAccess column on all 5 of the tables. Create database triggers on each table to update it's corresponding lastAccess column. This will allow you to track when each table is updated. Create lastAccess on Table 1. Then create triggers on tables 2,3,4,5 that will update the lastAccess column on Table 1.


1

Technically it is possible. For example: --drop table if exists t1; --drop table if exists t2; --drop function if exists foo(); create table t1(x int, q int); create table t2(y int, q int); create function foo() returns trigger language plpgsql as $foo$ begin raise info '%', tg_argv[0]; raise info '%', new; execute $$select (b).* from (select $1 #= ...


1

As @ypercubeᵀᴹ pointed out in the comments, How do I add a “last updated” column in a SQL Server 2008 R2 table? has the answer. Checking to see if the column has been updated just skips the logic and works the way I expect it to. When the user updates the column manually in takes their update, and doesn't cause recursion when they don't. For some reason I ...


1

I changed a few things so that it ended up like this: Create TRIGGER [sonfor_ags].[trg_date] ON [sonfor_ags].[MitigationMeasure] AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE t SET DateRemove = CURRENT_TIMESTAMP FROM [sonfor_ags].[MitigationMeasure] AS t INNER JOIN inserted AS i ON t.OBJECTID = i.OBJECTID AND i.Removed = 1; END GO ...


1

Tested on 5.5.34 I looked at this in detail (I'm interested for an upcoming project, and it's been a while since I've "got my hands dirty"...) It appears that datetimes are hard to set defaults for - see here. From what I've read, things have got better in 5.6 - I'll see if I have time to test tomorrow. I think that your best solution is as below. The ...


1

If like in your case, you want to change the updated field every time the record has been changed, you do not need a trigger. You can use MySQL's automatic initialization/updating like this: ALTER TABLE `yourtabelname` ADD `updated` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP Or at the time of table creation, from the docs: ...


Only top voted, non community-wiki answers of a minimum length are eligible