Yhd25J3k

1drop table if exists accounts;
2CREATE TABLE if not exists accounts
3(
4    user_id  serial primary key not null,
5    username varchar(20)        not null,
6    password varchar(16)        not null
7);
8drop table if exists output_log;
9CREATE TABLE if not exists output_log
10(
11    user_id    integer primary key not null,
12    event_time timestamp,
13    last_edit  timestamp
14);
15
16drop function if exists log_account_creation() cascade;
17
18CREATE OR REPLACE FUNCTION log_account_creation()
19    RETURNS TRIGGER
20    LANGUAGE plpgsql AS
21$$
22BEGIN
23    IF (TG_OP = 'DELETE') THEN
24        DELETE
25        FROM output_log
26        where user_id = old.user_id;
27        RETURN NEW;
28    ELSIF (TG_OP = 'UPDATE') THEN
29        UPDATE output_log
30        set last_edit = now()
31        where user_id = OLD.user_id;
32        RETURN NEW;
33    ELSIF (TG_OP = 'INSERT') THEN
34        INSERT INTO output_log (user_id, event_time)
35        values (NEW.user_id, now());
36        RETURN NEW;
37    END IF;
38END;
39$$;
40
41CREATE TRIGGER my_trigger
42    AFTER INSERT OR UPDATE OR DELETE
43    ON accounts
44    FOR EACH ROW
45EXECUTE PROCEDURE log_account_creation();
46
47Insert into accounts (username, password)
48values ('Prova', 'Prova123');
49
50UPDATE accounts
51set username='TEST'
52where username = 'Prova';
53
54
55DELETE
56FROM accounts
57where user_id = 1;
58
59select *
60from output_log;
61
62
63
64