title: TRIGGERS SQL: INSERT OR UPDATE In A Table that is Connected to Another Table
body: <p>I have this table called Teacher and School. I would like to create a trigger on the Teacher table to check and disallow any insert or update of a person in the Teacher table to be a teacher of more than one school. Schemas of the tables:</p>
  
  <pre><code>CREATE TABLE School (
    ID      char(3) primary key,
    Name    text not null,
    Address text default null,
  );
  
  CREATE TABLE Teacher (
    ID         char(3) references School(ID),
    Teacher_ID char(5)
    Name       text,
    primary key (ID, Teacher_ID)
  );
  </code></pre>
  
  <p>I'm fairly new to trigger and would like to get a better understanding on this problem. Does this mean I have to create two triggers: one for INSERT and another one for UPDATE? Do I have to create a VIEW to help count if the person is already a teacher of a school? This is what I got so far, still empty, messy and wrong but would greatly appreciate it if someone can help me explain parts by parts.</p>
  
  <pre><code>create or replace function
      insertPerson() returns trigger
  as $$
  declare
      t Teacher;
  begin
      select * into t from Teacher where ID = new.Teacher_ID;
      update Teacher
      return new;
  end;
  $$ language plpgsql;
  
  CREATE TRIGGER before_teacher_update 
      BEFORE UPDATE ON teacher
      FOR EACH ROW execute procedure insertPerson();
  </code></pre>