hxmH286m

1
2CREATE TABLE users(
3id serial PRIMARY key,
4username varchar(32) NOT NULL UNIQUE ,
5password varchar(32) NOT NULL ,
6email varchar(32) NOT NULL UNIQUE,
7created_at TIMESTAMP NOT NULL,
8updated_at TIMESTAMP NOT NULL
9);
10
11CREATE TABLE profiles(
12id serial PRIMARY key,
13user_id INTEGER NOT NULL ,
14name varchar(32),
15last_name varchar(32),
16photo_path varchar(32),
17about text,
18FOREIGN key (user_id) REFERENCES users(id)
19);
20
21— '===1==='
22
23CREATE OR replace FUNCTION new_profile() RETURNS TRIGGER
24AS $$
25BEGIN 
26INSERT INTO profiles(user_id) VALUES (NEW.id);
27RETURN NEW;
28END
29$$ LANGUAGE plpgsql;
30
31DROP TRIGGER if EXISTS tr_new_profile ON users ;
32
33CREATE TRIGGER tr_new_profile after INSERT ON users
34FOR EACH ROW EXECUTE PROCEDURE new_profile();
35
36— '===2==='
37
38CREATE OR replace FUNCTION update_user() RETURNS TRIGGER
39AS $$
40BEGIN 
41UPDATE users SET updated_at = now() WHERE id = OLD.user_id;
42RETURN old;
43END
44$$ LANGUAGE plpgsql;
45
46DROP TRIGGER if EXISTS tr_new_profile ON profiles ;
47
48CREATE TRIGGER tr_update_user after INSERT ON profiles
49FOR EACH ROW EXECUTE PROCEDURE update_user();
50
51— '===3==='
52
53CREATE OR replace FUNCTION delete_profile() RETURNS TRIGGER
54AS $$
55BEGIN 
56DELETE FROM profiles WHERE user_id = OLD.id;
57RETURN old;
58END
59$$ LANGUAGE plpgsql;
60DROP TRIGGER if EXISTS tr_delete_profile ON users ;
61
62CREATE TRIGGER tr_delete_profile before DELETE ON users
63FOR EACH ROW EXECUTE PROCEDURE update_user();