Stored Procedures & Triggers Cheat Sheet

CREATE OR REPLACE FUNCTION get_customer_total(p_customer_id INT)RETURNS NUMERIC AS $$DECLARE  v_total NUMERIC;BEGIN  SELECT COALESCE(SUM(total), 0) INTO v_total  FROM orders  WHERE customer_id = p_customer_id;  RETURN v_total;END;$$ LANGUAGE plpgsql;-- Call itSELECT get_customer_total(42);

DELIMITER $$CREATE PROCEDURE ApplyDiscount(IN p_order_id INT, IN p_pct DECIMAL(5,2))BEGIN  DECLARE v_total DECIMAL(10,2);  START TRANSACTION;  UPDATE orders  SET total = total - (total * p_pct / 100)  WHERE id = p_order_id;  COMMIT;END $$DELIMITER ;CALL ApplyDiscount(101, 10.00);

CREATE TABLE order_audit (  id SERIAL PRIMARY KEY,  order_id INT,  old_status TEXT,  new_status TEXT,  changed_at TIMESTAMP DEFAULT now());CREATE OR REPLACE FUNCTION log_status_change() RETURNS TRIGGER AS $$BEGIN  IF OLD.status IS DISTINCT FROM NEW.status THEN    INSERT INTO order_audit(order_id, old_status, new_status)    VALUES (OLD.id, OLD.status, NEW.status);  END IF;  RETURN NEW;END;$$ LANGUAGE plpgsql;CREATE TRIGGER trg_order_status_changeAFTER UPDATE ON ordersFOR EACH ROWEXECUTE FUNCTION log_status_change();

CREATE OR REPLACE FUNCTION transfer_funds(p_from INT, p_to INT, p_amount NUMERIC)RETURNS VOID AS $$BEGIN  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;  IF NOT FOUND THEN    RAISE EXCEPTION 'source account % not found', p_from USING ERRCODE = 'no_data_found';  END IF;  IF (SELECT balance FROM accounts WHERE id = p_from) < 0 THEN    RAISE EXCEPTION 'insufficient funds in account %', p_from USING ERRCODE = 'check_violation';  END IF;  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;EXCEPTION  WHEN check_violation THEN    RAISE NOTICE 'transfer rolled back: %', SQLERRM;    RAISE;  WHEN OTHERS THEN    RAISE WARNING 'unexpected error in transfer_funds: % (SQLSTATE %)', SQLERRM, SQLSTATE;    RAISE;END;$$ LANGUAGE plpgsql;

CREATE VIEW dbo.vw_order_summary ASSELECT o.id, o.customer_id, c.name AS customer_name, o.totalFROM orders oJOIN customers c ON c.id = o.customer_id;GOCREATE TRIGGER trg_order_summary_updateON dbo.vw_order_summaryINSTEAD OF UPDATEASBEGIN  SET NOCOUNT ON;  UPDATE o  SET o.total = i.total  FROM orders o  JOIN inserted i ON i.id = o.id;  -- inserted/deleted pseudo-tables expose the batch of rows being changed  IF UPDATE(customer_name)    RAISERROR('customer_name is derived and cannot be updated directly', 16, 1);END;

DELIMITER $$CREATE PROCEDURE GetOrgChart(IN p_root_id INT)BEGIN  WITH RECURSIVE org AS (    SELECT id, name, manager_id, 1 AS depth    FROM employees    WHERE id = p_root_id    UNION ALL    SELECT e.id, e.name, e.manager_id, org.depth + 1    FROM employees e    JOIN org ON e.manager_id = org.id  )  SELECT * FROM org ORDER BY depth, name;END $$DELIMITER ;CALL GetOrgChart(1);

CREATE OR REPLACE FUNCTION propagate_parent_total() RETURNS TRIGGER AS $$BEGIN  -- Bail out if this trigger is already nested inside itself  IF pg_trigger_depth() > 1 THEN    RETURN NEW;  END IF;  UPDATE categories  SET running_total = running_total + (NEW.amount - COALESCE(OLD.amount, 0))  WHERE id = NEW.category_id;  RETURN NEW;END;$$ LANGUAGE plpgsql;CREATE TRIGGER trg_propagate_totalAFTER INSERT OR UPDATE ON transactionsFOR EACH ROWEXECUTE FUNCTION propagate_parent_total();