BEFORE और AFTER Triggers

BEFORE vs AFTER का Exact अंतर

MySQL trigger एक action time—BEFORE/AFTER—को INSERT, UPDATE या DELETE event से जोड़ता है। Timing हर affected row के relative है:

• BEFORE: row change से पहले permitted NEW value validate, reject या assign।
• AFTER: row operation successful होने के बाद audit/dependent row।

Timing, Events और Value Access

OLD हमेशा read-only। Generated columns को NEW.column या OLD.column से refer नहीं कर सकते। AUTO_INCREMENT explicitly set न हो तो BEFORE trigger में NEW value 0 होती है। Basic column checks trigger activation से पहले हो सकते हैं।

Reproducible Timing Lab

DROP TABLE IF EXISTS order_timing_audit;
DROP TABLE IF EXISTS orders_timing_lab;
CREATE TABLE orders_timing_lab (
  order_id INT PRIMARY KEY,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  total_amount DECIMAL(10,2) NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
  CHECK (status IN ('PENDING','PAID','CANCELLED'))
) ENGINE = InnoDB;
CREATE TABLE order_timing_audit (
  audit_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  old_status VARCHAR(20), new_status VARCHAR(20),
  old_amount DECIMAL(10,2), new_amount DECIMAL(10,2),
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

CHECK finite status संभालता है। Triggers normalization, cross-column cancellation rule और audit जोड़ेंगे। Responsibilities split होने से test आसान है।

BEFORE INSERT और UPDATE Examples

DELIMITER //
CREATE TRIGGER bi_orders_timing
BEFORE INSERT ON orders_timing_lab
FOR EACH ROW
BEGIN
  SET NEW.status = UPPER(TRIM(NEW.status));
  IF NEW.total_amount < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'total_amount cannot be negative';
  END IF;
END //
CREATE TRIGGER bu_orders_timing
BEFORE UPDATE ON orders_timing_lab
FOR EACH ROW
BEGIN
  SET NEW.status = UPPER(TRIM(NEW.status));
  IF NEW.status = 'CANCELLED'
     AND NEW.total_amount > OLD.total_amount THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'cancelled order amount cannot increase';
  END IF;
END //
DELIMITER ;
INSERT INTO orders_timing_lab
  (order_id,status,total_amount)
VALUES (1,' paid ',1200.00);

Normalized status same row में store होना है इसलिए BEFORE सही है। SIGNAL '45000' silent meaning corruption की जगह deliberate business error देता है।

AFTER UPDATE: Final Change Record करें

DELIMITER //
CREATE TRIGGER au_orders_timing
AFTER UPDATE ON orders_timing_lab
FOR EACH ROW
BEGIN
  IF NOT (OLD.status <=> NEW.status)
     OR NOT (OLD.total_amount <=> NEW.total_amount) THEN
    INSERT INTO order_timing_audit
      (order_id,old_status,new_status,old_amount,new_amount)
    VALUES
      (NEW.order_id,OLD.status,NEW.status,
       OLD.total_amount,NEW.total_amount);
  END IF;
END //
DELIMITER ;
UPDATE orders_timing_lab
SET status=' cancelled ', total_amount=1100.00
WHERE order_id=1;

BEFORE UPDATE पहले NEW.status normalize करता है। AFTER वही final value audit करता है। NULL-safe <=> future nullable columns की transitions पकड़ता है।

AFTER, “Commit के बाद” नहीं है

SELECT COUNT(*) FROM order_timing_audit;
START TRANSACTION;
UPDATE orders_timing_lab
SET total_amount=1000.00 WHERE order_id=1;
SELECT total_amount FROM orders_timing_lab WHERE order_id=1;
SELECT COUNT(*) FROM order_timing_audit;
ROLLBACK;
SELECT total_amount FROM orders_timing_lab WHERE order_id=1;

दोनों tables InnoDB हैं, इसलिए changes transactional हैं। Nontransactional table same guarantee नहीं देती। True after-commit external notification के लिए application transaction और transactional outbox use करें।

Multiple Triggers और Activation Order

Same table/time/event के multiple triggers allowed हैं। Default creation order; required हो तो existing trigger के साथ PRECEDES/FOLLOWS:

CREATE TRIGGER bu_orders_timing_round
BEFORE UPDATE ON orders_timing_lab
FOR EACH ROW FOLLOWS bu_orders_timing
  SET NEW.total_amount = ROUND(NEW.total_amount, 2);

ACTION_ORDER को information_schema.TRIGGERS में inspect करें। Multiple dependent validation triggers के बजाय one cohesive trigger अक्सर समझना आसान है।

कौन-सा Timing चुनें और कैसे Test करें?

1. Valid, boundary, invalid, NULL test करें।
2. Multi-row statements test करें।
3. हर event में OLD/NEW verify करें।
4. Normalization before audit check करें।
5. Error, rollback, retry, deadlock test करें।
6. Order/definer inspect करें।
7. Bulk DML benchmark करें।

triggers, transactions और locks/deadlocks पढ़ें।

Official संदर्भ

• MySQL 8.4: CREATE TRIGGER
• MySQL 8.4: Trigger Syntax
• MySQL 8.4: Transactions, COMMIT and ROLLBACK

Timing, NEW/OLD write access, ordering और transactions official MySQL 8.4 documentation से verify किए गए हैं।