CREATE OR REPLACE FUNCTION object_deletion() RETURNS trigger AS $$
 BEGIN
  IF NEW.deleted != OLD.deleted OR ((new.deleted != old.deleted) IS NULL AND (new.deleted IS NOT NULL OR old.deleted IS NOT NULL)) THEN
    UPDATE object SET deleted = new.deleted WHERE objectid = new.id;
    IF (NEW.deleted) THEN
      DELETE FROM resource WHERE linkedprimarykey = NEW.id AND linkedtable IN ('object', 'region', 'street', 'company');
      UPDATE company_role SET deleted = TRUE WHERE targetid = NEW.id;
      UPDATE company_role SET deleted = TRUE WHERE companyid = NEW.id;
    END IF;
    RETURN NULL;
  END IF;

  RETURN NULL;
 END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION object_activate() RETURNS trigger AS $$
 BEGIN
  IF NEW.active != OLD.active OR ((NEW.active != OLD.active) IS NULL AND (new.deleted IS NOT NULL OR old.deleted IS NOT NULL)) THEN
    UPDATE object SET active = NEW.active WHERE objectid = new.id;
    IF (NOT NEW.active) THEN
      DELETE FROM resource WHERE linkedprimarykey = NEW.id AND linkedtable IN ('object', 'region', 'street', 'company');
    END IF;
    RETURN NULL;
  END IF;

  RETURN NULL;
 END
$$ LANGUAGE plpgsql;

DROP TRIGGER delete_structure ON structure;
DROP TRIGGER delete_region ON region;
DROP TRIGGER delete_company ON company;
DROP TRIGGER delete_street ON street;

CREATE TRIGGER delete_structure AFTER UPDATE OF deleted ON structure FOR EACH ROW EXECUTE PROCEDURE object_deletion();
CREATE TRIGGER delete_region AFTER UPDATE OF deleted ON region FOR EACH ROW EXECUTE PROCEDURE object_deletion();
CREATE TRIGGER delete_company AFTER UPDATE OF deleted ON company FOR EACH ROW EXECUTE PROCEDURE object_deletion();
CREATE TRIGGER delete_street AFTER UPDATE OF deleted ON street FOR EACH ROW EXECUTE PROCEDURE object_deletion();

CREATE TRIGGER active_structure AFTER UPDATE OF active ON structure FOR EACH ROW EXECUTE PROCEDURE object_activate();
CREATE TRIGGER active_region AFTER UPDATE OF active ON region FOR EACH ROW EXECUTE PROCEDURE object_activate();
CREATE TRIGGER active_company AFTER UPDATE OF active ON company FOR EACH ROW EXECUTE PROCEDURE object_activate();
CREATE TRIGGER active_street AFTER UPDATE OF active ON street FOR EACH ROW EXECUTE PROCEDURE object_activate();

CREATE OR REPLACE FUNCTION structure_activate() RETURNS trigger AS $$
 BEGIN
  IF NEW.structural_form IS NOT NULL AND NEW.status IS NOT NULL AND (NEW.address IS NOT NULL OR NEW.name IS NOT NULL) THEN
    UPDATE structure SET active = TRUE WHERE id = NEW.id;
  ELSE
    UPDATE structure SET active = FALSE WHERE id = NEW.id;
  END IF;

  RETURN NULL;
 END
$$ LANGUAGE plpgsql;

CREATE TRIGGER activate_structure AFTER UPDATE OF structural_form, status, address, name ON structure FOR EACH ROW EXECUTE PROCEDURE structure_activate();

CREATE OR REPLACE FUNCTION flattable_deletion() RETURNS trigger AS $$
 BEGIN
  IF new.deleted != old.deleted OR ((new.deleted != old.deleted) IS NULL AND (new.deleted IS NOT NULL OR old.deleted IS NOT NULL)) THEN
    EXECUTE 'UPDATE '||quote_ident(new.objecttypeid)||' SET deleted = '||new.deleted||' WHERE id = '||new.objectid;
  END IF;

  RETURN NULL;
 END;
$$ LANGUAGE plpgsql;

DROP TRIGGER delete_object ON object;
-- CREATE TRIGGER delete_object AFTER UPDATE OF deleted ON object FOR EACH ROW EXECUTE PROCEDURE flattable_deletion();
