MySQL Triggers

MySQL Trigger क्या है?

Trigger permanent table से जुड़ा named database object है। Configured INSERT, UPDATE या DELETE event से affected हर row के लिए automatically activate होता है। इसे CALL नहीं करते।

हर writer पर लागू rule, bounded derived value और small audit row इसके अच्छे uses हैं। Long workflows, network calls या hidden application logic के लिए trigger सही जगह नहीं।

CREATE TRIGGER और NEW/OLD Matrix

CREATE TRIGGER trigger_name
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
  ON table_name FOR EACH ROW
  trigger_body;

BEFORE trigger में permitted SET NEW.column new value बदल सकता है। OLD read-only है। AFTER में NEW/OLD से row values बदल नहीं सकते।

Triggers row-level हैं: 500 rows वाला UPDATE trigger 500 बार activate करता है। TRUNCATE और DROP TABLE DELETE triggers activate नहीं करते।

Reproducible Orders Audit Lab

DROP TABLE IF EXISTS order_audit_lab;
DROP TABLE IF EXISTS orders_trigger_lab;
CREATE TABLE orders_trigger_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  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,
  CONSTRAINT chk_order_status
    CHECK (status IN ('PENDING','PAID','CANCELLED'))
) ENGINE = InnoDB;

CREATE TABLE order_audit_lab (
  audit_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  action_name VARCHAR(20) NOT NULL,
  old_status VARCHAR(20), new_status VARCHAR(20),
  old_amount DECIMAL(10,2), new_amount DECIMAL(10,2),
  changed_by VARCHAR(288) NOT NULL,
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX ix_audit_order_time (order_id, changed_at)
) ENGINE = InnoDB;

Finite status domain CHECK constraint संभालती है। Cross-column amount rule और audit trigger संभालेगा। जो rule declarative constraint से हो सके उसके लिए constraint prefer करें।

Validation और Normalization का BEFORE Trigger

DELIMITER //
CREATE TRIGGER bi_orders_validate
BEFORE INSERT ON orders_trigger_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 //
DELIMITER ;

INSERT INTO orders_trigger_lab
  (order_id, customer_id, status, total_amount)
VALUES (1, 101, ' paid ', 1200.00);
SELECT order_id, status, total_amount FROM orders_trigger_lab;

Basic column type checks trigger से पहले होते हैं; BEFORE trigger column type के लिए already invalid input को rescue नहीं कर सकता। Fast feedback के लिए application validation भी रखें, पर all writers के rule को database में enforce करें।

Audit Trail का AFTER UPDATE Trigger

DELIMITER //
CREATE TRIGGER au_orders_audit
AFTER UPDATE ON orders_trigger_lab
FOR EACH ROW
BEGIN
  IF NOT (OLD.status <=> NEW.status)
     OR NOT (OLD.total_amount <=> NEW.total_amount) THEN
    INSERT INTO order_audit_lab
      (order_id, action_name, old_status, new_status,
       old_amount, new_amount, changed_by)
    VALUES
      (NEW.order_id, 'UPDATE', OLD.status, NEW.status,
       OLD.total_amount, NEW.total_amount, SESSION_USER());
  END IF;
END //
DELIMITER ;

UPDATE orders_trigger_lab
SET status='CANCELLED', total_amount=1100.00
WHERE order_id=1;

NULL-safe <=> NULL transitions miss नहीं होने देता। SESSION_USER() connected account देता है; trigger में CURRENT_USER() normally definer बताता है। Shared DB account होने पर trusted application actor अलग capture करें।

Transaction, Rollback और Trigger Order

AFTER row operation के बाद है, COMMIT के बाद नहीं। InnoDB में row और trigger side effects same statement/transaction का part हैं:

START TRANSACTION;
UPDATE orders_trigger_lab SET status='PAID' WHERE order_id=1;
SELECT COUNT(*) FROM order_audit_lab;
ROLLBACK;
SELECT status FROM orders_trigger_lab WHERE order_id=1;

Trigger error raise करे तो statement fail होता है। बाकी transaction outcome error और handling पर निर्भर है; application intended rollback करे। Same event/timing के multiple triggers allowed हैं। Default creation order; dependency explicit करें:

CREATE TRIGGER au_orders_metrics
AFTER UPDATE ON orders_trigger_lab
FOR EACH ROW FOLLOWS au_orders_audit
  INSERT INTO metrics_queue(order_id,event_name)
  VALUES (NEW.order_id,'ORDER_UPDATED');

Independent triggers prefer करें; fragile side-effect chain deployment और reasoning कठिन बनाती है।

Triggers Inspect, Replace और Drop करें

SHOW TRIGGERS LIKE 'orders_trigger_lab';
SHOW CREATE TRIGGER au_orders_audit;
SELECT TRIGGER_NAME, ACTION_TIMING, EVENT_MANIPULATION,
       ACTION_ORDER, DEFINER
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
  AND EVENT_OBJECT_TABLE = 'orders_trigger_lab';
DROP TRIGGER IF EXISTS au_orders_audit;

• Create के लिए table पर TRIGGER privilege चाहिए।
• Definer security context चलता है; durable least-privileged account रखें।
• Definitions migrations/source control में रखें।
• Audit INSERT में columns name करें।
• Multi-row DML benchmark करें क्योंकि work per row multiply होता है।

Trigger Design और Test Checklist

1. पहले CHECK, FK और generated columns देखें।
2. Timing/event/table वाला clear name रखें।
3. INSERT, UPDATE, DELETE और NEW/OLD test करें।
4. One row, many rows, zero rows और NULL transitions test करें।
5. Errors और explicit rollback test करें।
6. Body deterministic, bounded और external work से free रखें।
7. Lookup/audit columns index करें।
8. Locks, deadlocks, replication, restore review करें।
9. Definer, permissions और order document करें।
10. Latency और audit growth monitor करें।

BEFORE/AFTER, procedures और transactions पढ़ें।

Official संदर्भ

• MySQL 8.4: CREATE TRIGGER
• MySQL 8.4: Trigger Syntax and Examples
• MySQL 8.4: Stored Program Restrictions

Timing, events, NEW/OLD, ordering, privileges और restrictions official MySQL 8.4 manual से verify किए गए हैं।