SQLite: Is it possible to create a trigger on table A that inserts/updates entity in table B?-CodePudding

So I have some 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 1-1 trigger on each table that runs after inserting/updating in given tables. What the trigger should do is it should save the biggest datetime from given table into the TableCollected table.

So if TableAAA table contains 3 rows with following dates: 1980-01-01, 1990-01-01, 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 2 rows with dates 1999-01-01, 2012-04-01, TableCollected should have 2 rows:

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

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

CodePudding user response：

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.

CodePudding user response：

The answer to your question is Yes. Here is an example from the official docs:

CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;