title: PostgreSQL multiple triggers and functions
body: <p>I have the following tables. </p>
  
  <p><strong>tbl_groups</strong> contains all the current data, and I'm storing the name in a history table when it changes.</p>
  
  <pre><code>CREATE TABLE tbl_groups (
      id integer NOT NULL,
      name varchar NOT NULL,
      active boolean NOT NULL
  );
  
  CREATE TABLE tbl_groups_history (
      id integer NOT NULL,
      group_id integer NOT NULL,
      name varchar NOT NULL        
  );
  </code></pre>
  
  <p>When the active field is false an update may not occur. So I created a trigger for this to throw an exception when this happens:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION fn_group_deny_update_when_inactive() RETURNS TRIGGER AS
  $BODY$
      BEGIN
          IF 
              OLD.active = false
          THEN 
              RAISE EXCEPTION 'Record is inactive';
          END IF;
          RETURN NEW;
      END;
  $BODY$
  LANGUAGE plpgsql;
  
  CREATE TRIGGER 01_group_can_update
  BEFORE UPDATE ON tbl_groups
  FOR EACH ROW
  EXECUTE PROCEDURE fn_group_deny_update_when_inactive();
  </code></pre>
  
  <p>I also have a trigger that writes a record to the history table</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION fn_group_log_history() RETURNS trigger as 
  $BODY$
      BEGIN
          IF
             (NEW.name &lt;&gt; OLD.name)       
          THEN
              INSERT INTO tbl_groups_history (group_id, name)
              VALUES (OLD.id, OLD.name);
          END IF;
          RETURN NEW;
      END;
  $BODY$
  LANGUAGE plpgsql;
  
  CREATE TRIGGER 02_group_write_history_log
  BEFORE UPDATE ON tbl_groups
  FOR EACH ROW
  EXECUTE PROCEDURE fn_group_log_history();
  </code></pre>
  
  <p>I have named the triggers with 01 and 02 as a prefix, so I know in what order they will be executed as PostgreSQL fires them in alphabetical order.</p>
  
  <p>I have some questions about this approach as I'm not sure if it's good practice:</p>
  
  <ol>
  <li>Is it possible to have one BEFORE UPDATE on tbl_groups trigger that executes those 2 functions in a specific order?</li>
  <li>Is it better to have just one function that does a check on the "active" field first, and if it continues, do the history stuff? So I will have just 1 (bigger) function and only 1 trigger.</li>
  </ol>
  
  <p>Everything in one function:</p>
  
  <pre><code> CREATE OR REPLACE FUNCTION fn_group_before_update() RETURNS trigger as 
  $BODY$
      BEGIN
          IF 
              OLD.active = false
          THEN 
              RAISE EXCEPTION 'Record is inactive';
          END IF;
          IF
             (NEW.name &lt;&gt; OLD.name)       
          THEN
              INSERT INTO tbl_groups_history (group_id, name)
              VALUES (OLD.id, OLD.name);
          END IF;
          RETURN NEW;
      END;
  $BODY$
  </code></pre>
  
  <p>Any thoughts about this considering performance, maintenance of code and stuff like that?</p>