Message Center Note Link

ALTER TABLE actor.usr_message
ADD COLUMN usr_note bigint REFERENCES actor.usr_note (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;

CREATE OR REPLACE FUNCTION actor.convert_usr_note_to_message()
  RETURNS trigger AS
$BODY$
DECLARE
    sending_ou INTEGER;
    orig_message INTEGER;
    orig_deleted BOOLEAN;
BEGIN
    IF TG_OP = 'UPDATE' THEN
        IF OLD.pub = NEW.pub THEN
            RETURN NEW;
        END IF;
        SELECT INTO orig_message, orig_deleted id, deleted FROM actor.usr_message WHERE usr_note = NEW.id;
    END IF;

    IF NEW.pub AND orig_message IS NULL THEN
        SELECT INTO sending_ou aw.owning_lib
        FROM auditor.get_audit_info() agai
        JOIN actor.workstation aw ON (aw.id = agai.eg_ws);
        IF sending_ou IS NULL THEN
            SELECT INTO sending_ou home_ou
            FROM actor.usr
            WHERE id = NEW.creator;
        END IF;
        INSERT INTO actor.usr_message (usr, title, message, sending_lib, usr_note)
            VALUES (NEW.usr, NEW.title, NEW.value, sending_ou, NEW.id);
    END IF;

    IF orig_message IS NOT NULL AND orig_deleted = NEW.pub THEN
        UPDATE actor.usr_message SET deleted = NOT NEW.pub WHERE id = orig_message;
    END IF;

    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION actor.update_usr_note_from_message()
  RETURNS trigger AS
$BODY$
BEGIN
    IF NEW.usr_note IS NOT NULL AND NEW.deleted <> OLD.deleted THEN
        UPDATE actor.usr_note SET pub = NOT NEW.deleted WHERE id = NEW.usr_note;
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_usr_note_from_message_tgr AFTER UPDATE ON actor.usr_message FOR EACH ROW EXECUTE PROCEDURE actor.update_usr_note_from_message();

CREATE OR REPLACE RULE usr_note_delete_message AS ON DELETE TO actor.usr_note DO ALSO UPDATE actor.usr_message aum SET deleted = TRUE, usr_note = NULL WHERE aum.usr_note = old.id;