Lt6jUFfn

1DELIMITER //
2
3DROP TABLE IF EXISTS person;
4DROP TABLE IF EXISTS wohnort;
5
6
7CREATE TABLE person(
8   id integer  primary key,
9   first_name varchar(40) NOT NULL,
10   last_name varchar(40) NOT NULL,
11   updated_at timestamp(6)
12);
13
14CREATE TABLE wohnort (
15   id integer primary key,
16   plz varchar NOT NULL,
17   ort varchar(40) NOT NULL,
18   updated_at timestamp(6) NOT NULL
19);
20
21CREATE OR REPLACE FUNCTION log_last_name_changes()
22  RETURNS trigger LANGUAGE plpgsql AS $$
23BEGIN
24 IF NEW.last_name <> OLD.last_name THEN
25 UPDATE person SET updated_at = NOW() WHERE id = OLD.id;
26 END IF;
27 
28 RETURN NEW;
29END;
30$$;
31
32CREATE TRIGGER last_name_changes
33  AFTER Update
34  ON person
35  FOR EACH ROW
36  EXECUTE PROCEDURE log_last_name_changes();
37
38INSERT INTO person (id, first_name, last_name)
39VALUES (1, 'Andre', 'Muster');
40
41UPDATE person
42SET last_name = 'bammmmm'
43WHERE ID = 1;