init.sql

DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS students_partial;
DROP FUNCTION IF EXISTS trigger_students;
DROP TRIGGER IF EXISTS students_trigger ON students;
DROP FUNCTION IF EXISTS trigger_students_delete;
DROP TRIGGER IF EXISTS students_delete_trigger ON students;

create table students (
    id character(5) constraint pk1 primary key,
    student_name varchar(255) not null,
    last_name varchar(255) not null,
    sex character(1) not null,
    address varchar(255),
    iban character(27),
    disability varchar(255)
);

create table students_partial (
    id character(5) primary key references students,
    student_name varchar(255) not null,
    last_name varchar(255) not null,
    sex character(1) not null
);

CREATE OR REPLACE FUNCTION trigger_students()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO students_partial (id, student_name, last_name, sex)
    VALUES (NEW.id, NEW.student_name, NEW.last_name, NEW.sex);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER students_trigger
AFTER INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION trigger_students();

CREATE OR REPLACE FUNCTION trigger_students_delete()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM students_partial
    WHERE id = OLD.id;
    
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER students_delete_trigger
BEFORE DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION trigger_students_delete();

INSERT INTO students (id, student_name, last_name, sex, address, iban, disability)
VALUES 
    ('12345', 'John', 'Smith', 'M', '123 Main St', 'US1234567890123456789012345', null),
    ('67890', 'Michael', 'Johnson', 'M', null, null, null),
    ('54321', 'David', 'Williams', 'M', '456 Elm St', 'US1234567890123456789012346', null),
    ('98765', 'James', 'Brown', 'M', '789 Oak St', 'US1234567890123456789012347', 'dyslexia'),
    ('23456', 'Robert', 'Jones', 'M', '321 Pine St', 'US1234567890123456789012348', null),
    ('13579', 'Jennifer', 'Davis', 'F', null, null, null),
    ('24680', 'Jessica', 'Miller', 'F', '654 Cedar St', 'US1234567890123456789012349', null),
    ('86420', 'Sarah', 'Wilson', 'F', null, null, null),
    ('97531', 'Emily', 'Taylor', 'F', '987 Maple St', 'US1234567890123456789012350', 'dyscalculia'),
    ('75319', 'Emma', 'Anderson', 'F', '654 Oak St', 'US1234567890123456789012351', null),
    ('46802', 'Olivia', 'Thomas', 'F', '321 Birch St', 'US1234567890123456789012352', null);