hX6KE5Pb

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  
38CREATE OR REPLACE RULE check_plz AS ON INSERT TO wohnort WHERE
39plz = '123' DO INSTEAD NOTHING;
40
41INSERT INTO person (id, first_name, last_name)
42VALUES (1, 'Andre', 'Muster');
43
44INSERT INTO wohnort(id, plz, ort, updated_at)
45VALUES (1, '123', 'Remagen', NOW());
46
47UPDATE person
48SET last_name = 'bummm'
49WHERE ID = 1;