DatabaseProgramming: triggers.sql

-- =============================================================================
-- TRIGGER 1-3: User Subtype Enforcement
-- =============================================================================
-- A row in Client_User, Vendor_User or Management_User is accepted only when
-- "User".type names that subtype. Because the type cannot change afterwards
-- (trg_user_type_immutable), no user ever has rows in two subtype tables.
CREATE OR REPLACE FUNCTION trg_enforce_user_subtype()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  v_expected text;
  v_actual   text;
BEGIN
  v_expected := CASE TG_TABLE_NAME
                  WHEN 'client_user'     THEN 'client'
                  WHEN 'vendor_user'     THEN 'vendor'
                  WHEN 'management_user' THEN 'management'
                  ELSE NULL
                END;

  IF v_expected IS NULL THEN
    RAISE EXCEPTION 'trg_enforce_user_subtype is not valid on table %', TG_TABLE_NAME;
  END IF;

  SELECT type INTO v_actual FROM "User" WHERE user_id = NEW.user_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'User % does not exist', NEW.user_id;
  END IF;

  IF v_actual <> v_expected THEN
    RAISE EXCEPTION 'User % type must be % (it is %)', NEW.user_id, v_expected, v_actual;
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_client_user_subtype
  BEFORE INSERT OR UPDATE ON Client_User
  FOR EACH ROW EXECUTE FUNCTION trg_enforce_user_subtype();

CREATE OR REPLACE TRIGGER trg_vendor_user_subtype
  BEFORE INSERT OR UPDATE ON Vendor_User
  FOR EACH ROW EXECUTE FUNCTION trg_enforce_user_subtype();

CREATE OR REPLACE TRIGGER trg_management_user_subtype
  BEFORE INSERT OR UPDATE ON Management_User
  FOR EACH ROW EXECUTE FUNCTION trg_enforce_user_subtype();


-- =============================================================================
-- TRIGGER 4: User Type Immutability
-- =============================================================================
-- Keeps "User".type fixed, so the subtype row written for it cannot become
-- inconsistent.
CREATE OR REPLACE FUNCTION trg_prevent_user_type_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'User % type must stay % and cannot be changed', OLD.user_id, OLD.type;
END;
$$;

CREATE OR REPLACE TRIGGER trg_user_type_immutable
  BEFORE UPDATE OF type ON "User"
  FOR EACH ROW
  WHEN (NEW.type <> OLD.type)
  EXECUTE FUNCTION trg_prevent_user_type_change();


-- =============================================================================
-- TRIGGER 5: Vendor Subscription Negotiated Price Validation
-- =============================================================================
CREATE OR REPLACE FUNCTION trg_enforce_negotiated_price()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  v_allows_custom bool;
BEGIN
  PERFORM fn_assert_tier_exists(NEW.tier_id);

  SELECT allows_custom_pricing
  INTO v_allows_custom
  FROM Subscription_Tier
  WHERE tier_id = NEW.tier_id;

  IF v_allows_custom AND NEW.negotiated_price IS NULL THEN
    RAISE EXCEPTION
      'negotiated_price is required for tiers with custom pricing (contract_id: %)', NEW.contract_id;
  END IF;

  IF NOT v_allows_custom AND NEW.negotiated_price IS NOT NULL THEN
    RAISE EXCEPTION
      'negotiated_price must be NULL for fixed-price tiers (contract_id: %)', NEW.contract_id;
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_vendor_subscription_pricing
  BEFORE INSERT OR UPDATE ON Vendor_Subscription
  FOR EACH ROW EXECUTE FUNCTION trg_enforce_negotiated_price();


-- =============================================================================
-- TRIGGER 6-12: Automatic updated_at Timestamp Maintenance
-- =============================================================================
CREATE OR REPLACE FUNCTION trg_set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_project_updated_at
  BEFORE UPDATE ON Project
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_vendor_subscription_updated_at
  BEFORE UPDATE ON Vendor_Subscription
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_cvc_updated_at
  BEFORE UPDATE ON Client_Vendor_Contract
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_pba_updated_at
  BEFORE UPDATE ON Project_Budget_Audit
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_review_updated_at
  BEFORE UPDATE ON Review
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_dispute_ticket_updated_at
  BEFORE UPDATE ON Dispute_Ticket
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

CREATE OR REPLACE TRIGGER trg_user_updated_at
  BEFORE UPDATE ON "User"
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();


-- =============================================================================
-- TRIGGER 13: Project Budget Change Audit
-- =============================================================================
CREATE OR REPLACE FUNCTION trg_capture_budget_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO Project_Budget_Audit (project_id, old_budget, new_budget)
  VALUES (OLD.project_id, OLD.budget, NEW.budget);
  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_project_budget_audit
  AFTER UPDATE ON Project
  FOR EACH ROW
  WHEN (OLD.budget IS DISTINCT FROM NEW.budget)
  EXECUTE FUNCTION trg_capture_budget_change();


-- =============================================================================
-- TRIGGER 14: Project Status History Actor Validation
-- =============================================================================
CREATE OR REPLACE FUNCTION trg_validate_status_history_actor()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  PERFORM fn_assert_project_exists(NEW.project_id);

  -- A vendor actor must belong to the vendor on the project
  IF NEW.vendor_user_id IS NOT NULL THEN
    PERFORM fn_assert_vendor_user_exists(NEW.vendor_user_id);

    IF NOT coalesce(fn_is_vendor_user_of_project(NEW.vendor_user_id, NEW.project_id), false) THEN
      RAISE EXCEPTION
        'vendor_user % does not belong to the vendor on project %',
        NEW.vendor_user_id, NEW.project_id;
    END IF;
  END IF;

  IF NEW.management_user_id IS NOT NULL THEN
    PERFORM fn_assert_management_user_exists(NEW.management_user_id);
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_status_history_actor
  BEFORE INSERT ON Project_Status_History
  FOR EACH ROW EXECUTE FUNCTION trg_validate_status_history_actor();


-- =============================================================================
-- TRIGGER 15: Vendor Subscription Overlap Prevention
-- =============================================================================
-- Active and inactive periods alike are checked. end_date is exclusive, so a
-- period may start on the day the previous one ends.
-- contract_id <> NEW.contract_id excludes the row itself on UPDATE; on INSERT
-- the SERIAL default is already assigned when the trigger runs.
CREATE OR REPLACE FUNCTION trg_prevent_subscription_overlap()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM Vendor_Subscription
     WHERE vendor_id    = NEW.vendor_id
       AND contract_id <> NEW.contract_id
       AND (NEW.end_date IS NULL OR start_date < NEW.end_date)
       AND (end_date    IS NULL OR end_date    > NEW.start_date)
  ) THEN
    RAISE EXCEPTION
      'Vendor % already has a subscription period overlapping % - %',
      NEW.vendor_id, NEW.start_date, coalesce(NEW.end_date::text, 'open');
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_vendor_subscription_overlap
  BEFORE INSERT OR UPDATE ON Vendor_Subscription
  FOR EACH ROW EXECUTE FUNCTION trg_prevent_subscription_overlap();


-- =============================================================================
-- TRIGGER 16: Dispute Ticket Resolution Validation
-- =============================================================================
CREATE OR REPLACE FUNCTION trg_validate_dispute_resolution()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.is_resolved = true AND OLD.is_resolved = false THEN
    IF NEW.assigned_management_user_id IS NULL THEN
      RAISE EXCEPTION
        'Dispute ticket % must have an assigned management user before it can be resolved', OLD.ticket_id;
    END IF;

    PERFORM fn_assert_management_user_exists(NEW.assigned_management_user_id);

    IF NEW.resolved_at IS NULL THEN
      NEW.resolved_at := NOW();
    END IF;
  END IF;

  IF OLD.is_resolved = true AND NEW.is_resolved = false THEN
    RAISE EXCEPTION 'Resolved dispute ticket % cannot be re-opened', OLD.ticket_id;
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_dispute_ticket_resolve
  BEFORE UPDATE ON Dispute_Ticket
  FOR EACH ROW EXECUTE FUNCTION trg_validate_dispute_resolution();


-- =============================================================================
-- TRIGGER 17: Disputed Review Publish Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION trg_prevent_publishing_disputed_review()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.is_published = true AND OLD.is_published = false THEN
    IF EXISTS (
      SELECT 1 FROM Dispute_Ticket
       WHERE review_id   = NEW.review_id
         AND is_resolved = false
    ) THEN
      RAISE EXCEPTION
        'Review % cannot be published while it has unresolved dispute tickets', NEW.review_id;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_review_publish_guard
  BEFORE UPDATE ON Review
  FOR EACH ROW EXECUTE FUNCTION trg_prevent_publishing_disputed_review();


-- =============================================================================
-- TRIGGER 18: Review Only for a Finished Project
-- =============================================================================
-- A review is the client's verdict on a finished engagement: the project must
-- be Completed or Cancelled.
CREATE OR REPLACE FUNCTION trg_require_finished_project()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  v_status text;
BEGIN
  SELECT ps.status_name
  INTO v_status
  FROM Project p
  JOIN Project_Status ps ON ps.status_id = p.status_id
  WHERE p.project_id = NEW.project_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Project % does not exist', NEW.project_id;
  END IF;

  IF v_status NOT IN ('Completed', 'Cancelled') THEN
    RAISE EXCEPTION 'Project % is %; a review needs a finished project', NEW.project_id, v_status;
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_review_finished_project
  BEFORE INSERT OR UPDATE OF project_id ON Review
  FOR EACH ROW EXECUTE FUNCTION trg_require_finished_project();