【PL/SQL】仮想列とトリガーの整合性設計｜制約との使い分け・mutating table・監査

CREATE TABLE order_lines (
  order_line_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  quantity      NUMBER NOT NULL,
  unit_price    NUMBER NOT NULL,
  tax_rate      NUMBER DEFAULT 0.10 NOT NULL,
  subtotal      NUMBER GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL,
  tax_amount    NUMBER GENERATED ALWAYS AS (quantity * unit_price * tax_rate) VIRTUAL,
  total_amount  NUMBER GENERATED ALWAYS AS (quantity * unit_price * (1 + tax_rate)) VIRTUAL
);

SELECT order_line_id,
       subtotal,
       tax_amount,
       total_amount
FROM order_lines;

CREATE TABLE customers (
  customer_id     NUMBER PRIMARY KEY,
  customer_code   VARCHAR2(30) NOT NULL,
  customer_name   VARCHAR2(200) NOT NULL,
  normalized_code VARCHAR2(30)
    GENERATED ALWAYS AS (UPPER(TRIM(customer_code))) VIRTUAL
);

CREATE UNIQUE INDEX ux_customers_normalized_code
ON customers(normalized_code);

CREATE TABLE contracts (
  contract_id   NUMBER PRIMARY KEY,
  start_date    DATE NOT NULL,
  end_date      DATE,
  status        VARCHAR2(20) NOT NULL,
  active_flag   NUMBER
    GENERATED ALWAYS AS (
      CASE
        WHEN end_date IS NULL OR end_date >= start_date THEN 1
        ELSE 0
      END
    ) VIRTUAL,
  CONSTRAINT ck_contract_period
    CHECK (end_date IS NULL OR end_date >= start_date),
  CONSTRAINT ck_contract_status
    CHECK (status IN ('DRAFT', 'ACTIVE', 'CLOSED'))
);

CREATE INDEX ix_contracts_active_flag
ON contracts(active_flag);

CREATE TABLE api_events (
  event_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  payload      CLOB CHECK (payload IS JSON),
  event_type   VARCHAR2(50)
    GENERATED ALWAYS AS (
      JSON_VALUE(payload, '$.type' RETURNING VARCHAR2(50))
    ) VIRTUAL,
  tenant_id    VARCHAR2(50)
    GENERATED ALWAYS AS (
      JSON_VALUE(payload, '$.tenantId' RETURNING VARCHAR2(50))
    ) VIRTUAL
);

CREATE INDEX ix_api_events_tenant_type
ON api_events(tenant_id, event_type);

CREATE OR REPLACE TRIGGER trg_orders_biu
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    :NEW.created_at := SYSTIMESTAMP;
    :NEW.created_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
  END IF;

  :NEW.updated_at := SYSTIMESTAMP;
  :NEW.updated_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
END;
/

CREATE TABLE order_audit_logs (
  audit_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id      NUMBER NOT NULL,
  action_name   VARCHAR2(20) NOT NULL,
  old_status    VARCHAR2(20),
  new_status    VARCHAR2(20),
  changed_by    VARCHAR2(128),
  changed_at    TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE OR REPLACE TRIGGER trg_orders_audit
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (OLD.status <> NEW.status)
BEGIN
  INSERT INTO order_audit_logs (
    order_id,
    action_name,
    old_status,
    new_status,
    changed_by
  ) VALUES (
    :NEW.order_id,
    'STATUS_CHANGE',
    :OLD.status,
    :NEW.status,
    SYS_CONTEXT('USERENV', 'SESSION_USER')
  );
END;
/

CREATE OR REPLACE TRIGGER trg_orders_check_ng
AFTER INSERT OR UPDATE ON order_lines
FOR EACH ROW
DECLARE
  l_total NUMBER;
BEGIN
  SELECT SUM(amount)
  INTO l_total
  FROM order_lines
  WHERE order_id = :NEW.order_id;

  IF l_total > 1000000 THEN
    RAISE_APPLICATION_ERROR(-20001, 'order total limit exceeded');
  END IF;
END;
/

CREATE OR REPLACE TRIGGER trg_order_lines_limit
FOR INSERT OR UPDATE OR DELETE ON order_lines
COMPOUND TRIGGER

  TYPE t_order_ids IS TABLE OF order_lines.order_id%TYPE INDEX BY PLS_INTEGER;
  g_order_ids t_order_ids;
  g_count     PLS_INTEGER := 0;

  AFTER EACH ROW IS
  BEGIN
    g_count := g_count + 1;
    g_order_ids(g_count) := COALESCE(:NEW.order_id, :OLD.order_id);
  END AFTER EACH ROW;

  AFTER STATEMENT IS
    l_total NUMBER;
  BEGIN
    FOR i IN 1 .. g_count LOOP
      SELECT SUM(amount)
      INTO l_total
      FROM order_lines
      WHERE order_id = g_order_ids(i);

      IF l_total > 1000000 THEN
        RAISE_APPLICATION_ERROR(-20001, 'order total limit exceeded');
      END IF;
    END LOOP;
  END AFTER STATEMENT;

END trg_order_lines_limit;
/

CREATE OR REPLACE TRIGGER trg_orders_validate
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
BEGIN
  IF :NEW.order_status = 'SHIPPED'
     AND :NEW.shipped_at IS NULL THEN
    RAISE_APPLICATION_ERROR(
      -20010,
      'shipped_at is required when order_status is SHIPPED'
    );
  END IF;
END;
/

SELECT object_name,
       object_type,
       status
FROM user_objects
WHERE object_type = 'TRIGGER'
ORDER BY status, object_name;

SELECT name,
       type,
       referenced_name,
       referenced_type
FROM user_dependencies
WHERE type = 'TRIGGER'
ORDER BY name, referenced_type, referenced_name;

ALTER TRIGGER trg_orders_biu COMPILE;

-- Virtual columns
SELECT table_name,
       column_name,
       data_default
FROM user_tab_cols
WHERE virtual_column = 'YES'
ORDER BY table_name, column_id;

-- Triggers
SELECT trigger_name,
       table_name,
       triggering_event,
       trigger_type,
       status
FROM user_triggers
ORDER BY table_name, trigger_name;

-- Invalid objects
SELECT object_name,
       object_type,
       status
FROM user_objects
WHERE status <> 'VALID'
ORDER BY object_type, object_name;