Re: INSERT or UPDATE TRIGGER

["James B. Byrne" <byrnejb@xxxxxxxxxxxxx>]

This is what I have come up with.  Comments are welcomed.


      CREATE OR REPLACE FUNCTION hll_pg_fn_ident_insert()
      RETURNS TRIGGER AS $pg_fn$
        -- ROW AFTER TRIGGER
        -- trigger passes identifier_type, _value and _description
        -- received as ARGV[0], ARGV[1] and ARGV[2]
      BEGIN
        INSERT INTO identifiers(
            entity_id,
            identifier_type,
            identifier_value,
            identifier_description)
          VALUES(
            NEW.id,
            TG_ARGV[0],
            TG.ARGV[1],
            TG_ARGV[2]);

      -- Assume the INSERT fails because of a unique key violation,
      --   (entity_id + identifier_type + identifier_value)
      --
      -- This does not matter since we only need ensure that this
      -- alias exists, so handle the exception and return:

      EXCEPTION
        WHEN unique_violation THEN
          -- do nothing
          NULL;
      END;
      $pg_fn$ LANGUAGE plpgsql;

      COMMENT ON FUNCTION hll_pg_fn_ident_insert IS
        'Used by entities trigger. Inserts a corresponding
identifiers row.'

      CREATE TRIGGER hll_pg_tr_entity_identifier_akna
        AFTER INSERT OR UPDATE ON entities
        FOR EACH ROW EXECUTE PROCEDURE hll_pg_fn_identifier_insert(
          "AKNA", entities.entity_common_name, "Common Name
auto-insert");

      COMMENT ON TRIGGER hll_pg_tr_entity_identifier_akna IS
        'Inserts an alias identifier for common name if one does not
exist'




-- 
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@xxxxxxxxxxxxx
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general