How to create a trigger on table A that inserts/updates entity in table B?

I have tables TableAAA, TableBBB, TableCCC and TableCollected. The AAA/BBB/CCC tables have a DateTime column:

table_aaa: DateTime modified
table_bbb: DateTime modified
table_ccc: DateTime modified

I’d like to have a 1-1 trigger on each table that runs after inserting/updating. It should save the biggest DateTime from given table into the TableCollected table. So if TableAAA table contains 3 rows with dates 1980-01-01, 1990-01-01 and 2010-01-01 then in TableCollected I’d have a row that contains the name of the table (or some identifier, it’s okay if I have to set it) and the biggest date table_collected: name (table_aaa), modified (2010-01-01).

If TableBBB has rows with 1999-01-01, 2012-04-01, TableCollected should have 2 rows:

name        datetime
table_aaa  2010-01-01
table_bbb  2012-04-01

So TableCollected would hold the biggest DateTime of other tables. It should contain each table’s DateTime value just once. The name is unique and if it inserted once, it should update every time a new row is inserted in table AAA/BBB/CCC and modified value is bigger than the old value.

Answer

You need to create 3 triggers for every table, one for insert, one for update and one for delete (if the most recently modified record is deleted):

CREATE TRIGGER table_aaa_insert AFTER INSERT ON table_aaa 
BEGIN
  INSERT OR REPLACE INTO TableCollected (name, modified) 
  SELECT 'table_aaa', max(modified) FROM table_aaa;
END;

CREATE TRIGGER table_aaa_update AFTER UPDATE ON table_aaa 
BEGIN
  INSERT OR REPLACE INTO TableCollected (name, modified) 
  SELECT 'table_aaa', max(modified) FROM table_aaa;
END;

CREATE TRIGGER table_aaa_delete AFTER DELETE ON table_aaa 
BEGIN
  INSERT OR REPLACE INTO TableCollected (name, modified) 
  SELECT 'table_aaa', max(modified) FROM table_aaa;
END;

Note that name must be a primary key or a unique field of TableCollected to take advantage of INSERT OR REPLACE

However, depending on the rate of insert/update/delete operations on those tables, it could be more efficient to replace your TableCollected table with a view which dynamically returns the desired values:

CREATE VIEW TableCollected (name, modified) as
  SELECT 'table_aaa', max(modified) from table_aaa
  UNION ALL
  SELECT 'table_bbb', max(modified) from table_bbb
  UNION ALL
  SELECT 'table_ccc', max(modified) from table_ccc;

In both solutions, an index on modified field in tables aaa, bbb, and ccc would be beneficial to performance.

EDIT: On second thought, a more efficient way which doesn’t require to calculate max(modified) every time a record is inserted or updated is this:

CREATE TRIGGER table_aaa_insert AFTER INSERT ON table_aaa 
BEGIN
  UPDATE TableCollected SET modified=NEW.modified WHERE name='table_aaa' AND modified<NEW.modified;
END;

CREATE TRIGGER table_aaa_update AFTER UPDATE ON table_aaa 
BEGIN
  UPDATE TableCollected SET modified=NEW.modified WHERE name='table_aaa' AND modified<NEW.modified;
END;

DELETE trigger doesn’t change because in that case you need to search for max(modified) anyway.

Note that this solution requires to pre-populate TableCollected with a row for each table. Also note that in the trigger modified refers to the TableCollected field, and NEW.modified refers to the table_aaa field which was just inserted or updated.