LvnJMuZ0

1drop table if exists t,t1;
2create table t(id int primary key, val int, created_date datetime, lastLoaded datetime);
3create table t1(archiveid  int auto_increment primary key,id int, val int, created_date datetime, lastLoaded datetime);
4drop trigger if exists t;
5delimiter $$
6create trigger t before insert on t
7for each row
8begin
9        if not exists(select 1 from t where id = new.id) then
10            set new.created_date = '2019-01-01 01:00:00';
11            set new.lastloaded = '2019-01-01 01:00:00';
12            insert into debug_table(msg1) values (concat(new.id,': notfound:',new.lastloaded));
13        end if;
14
15end $$
16
17drop trigger if exists t2;
18delimiter $$
19create trigger t2 before update on t
20for each row
21begin
22        set new.lastloaded = '2019-01-01 08:00:00';
23        insert into debug_table(msg1) values (concat(new.id,': before update:',new.lastloaded));
24        if new.val  <> old.val then
25            insert into t1(id,val,created_date,lastloaded) values (old.id,old.val,old.created_date,old.lastloaded);
26        end if;
27end $$
28delimiter ;
29
30truncate table debug_table;
31insert into t(id,val) values
32(1,10),(2,20),(3,30)
33on duplicate key
34update val = values(val);
35commit;
36
37select * from t;
38
39insert into t(id,val) values
40(1,10),(2,20),(3,35)
41on duplicate key
42update val = values(val);
43
44select * from t;
45+----+------+---------------------+---------------------+
46| id | val  | created_date        | lastLoaded          |
47+----+------+---------------------+---------------------+
48|  1 |   10 | 2019-01-01 01:00:00 | 2019-01-01 08:00:00 |
49|  2 |   20 | 2019-01-01 01:00:00 | 2019-01-01 08:00:00 |
50|  3 |   35 | 2019-01-01 01:00:00 | 2019-01-01 08:00:00 |
51+----+------+---------------------+---------------------+
523 rows in set (0.00 sec)
53select * from t1;
54+-----------+------+------+---------------------+---------------------+
55| archiveid | id   | val  | created_date        | lastLoaded          |
56+-----------+------+------+---------------------+---------------------+
57|         1 |    3 |   30 | 2019-01-01 01:00:00 | 2019-01-01 01:00:00 |
58+-----------+------+------+---------------------+---------------------+
591 row in set (0.00 sec)
60select * from debug_table;
61+----+--------------------------------------+
62| id | msg1                                 |
63+----+--------------------------------------+
64|  1 | 1: notfound:2019-01-01 01:00:00      |
65|  2 | 2: notfound:2019-01-01 01:00:00      |
66|  3 | 3: notfound:2019-01-01 01:00:00      |
67|  4 | 1: before update:2019-01-01 08:00:00 |
68|  5 | 2: before update:2019-01-01 08:00:00 |
69|  6 | 3: before update:2019-01-01 08:00:00 |
70+----+--------------------------------------+
716 rows in set (0.00 sec)