CREATE TABLE users(
id serial PRIMARY KEY,
username varchar(32) NOT NULL UNIQUE ,
password varchar(32) NOT NULL ,
email varchar(32) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL
);

CREATE TABLE profiles(
id serial PRIMARY KEY,
user_id INTEGER NOT NULL ,
NAME varchar(32),
last_name varchar(32),
photo_path varchar(32),
about TEXT,
FOREIGN KEY (user_id) REFERENCES users(id)
);

— '===1==='

CREATE OR REPLACE FUNCTION new_profile() RETURNS TRIGGER
AS $$
BEGIN
INSERT INTO profiles(user_id) VALUES (NEW.id);
RETURN NEW;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tr_new_profile ON users ;

CREATE TRIGGER tr_new_profile after INSERT ON users
FOR EACH ROW EXECUTE PROCEDURE new_profile();

— '===2==='

CREATE OR REPLACE FUNCTION update_user() RETURNS TRIGGER
AS $$
BEGIN
UPDATE users SET updated_at = NOW() WHERE id = OLD.user_id;
RETURN old;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tr_new_profile ON profiles ;

CREATE TRIGGER tr_update_user after INSERT ON profiles
FOR EACH ROW EXECUTE PROCEDURE update_user();

— '===3==='

CREATE OR REPLACE FUNCTION delete_profile() RETURNS TRIGGER
AS $$
BEGIN
DELETE FROM profiles WHERE user_id = OLD.id;
RETURN old;
END
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS tr_delete_profile ON users ;

CREATE TRIGGER tr_delete_profile before DELETE ON users
FOR EACH ROW EXECUTE PROCEDURE update_user();