स्टोर्ड प्रोसीजर, ट्रिगर और व्यू

यह क्यों ज़रूरी है

Database का logic आसानी से नज़र से छूट जाता है। यह उस application code से बाहर है जिसे ज़्यादातर reviews देखते हैं, migration scripts से बदलता है, और trigger अपनी table को छूने वाले हर path पर चुपचाप चलता है: API, admin का fix, nightly import। वहाँ का defect सीधे data बिगाड़ता है — दो बार बना refund, ship हो चुके order का stock वापस, orders को दो बार गिनने वाली report — और अक्सर हफ़्तों बाद, अपनी वजह से दूर, दिखता है।

इन routines में critical rules होते हैं: पैसा, stock, status changes, audit records। इन्हें सीधे call करना और इनकी छुई हर table जाँचना आपको ऐसे defects release से पहले पकड़ने देता है।

Stored procedures: database के अंदर चलने वाले operations

Stored procedure database में बनाई जाती है, आम तौर पर migration script से, और नाम से call होती है। यह input (IN), output (OUT) या INOUT parameters लेती है और इसमें IF branches, loops, कई INSERT, UPDATE और DELETE statements, और ORDER_NOT_CANCELLABLE जैसी explicit errors हो सकती हैं। Teams procedures इसलिए इस्तेमाल करती हैं ताकि multi-step operation data के पास रहे, या application tables को सीधे बदलने के अधिकार के बिना उसे चला सके।

Function इसका क़रीबी रिश्तेदार है: यह value लौटाता है और query के अंदर इस्तेमाल होता है, जैसे SELECT order_total(1042)। Procedure अकेले CALL (PostgreSQL, MySQL) या EXEC (SQL Server) से चलती है; PostgreSQL में सिर्फ़ procedure ही अपने body में commit या roll back कर सकती है। दोनों में से कोई भी पढ़ते समय यह list बनाएँ:

• Parameters और उनकी allowed values: equivalence partitioning और boundary values के inputs।
• हर branch (IF, CASE, early exits): हर एक को एक test चाहिए।
• हर table जो पढ़ी या लिखी जाती है, triggers के ज़रिए भी।
• हर error जो raise होती है, और क्या बीच की failure पहले हो चुके काम को undo करती है।

-- PostgreSQL and MySQL: call a procedure
CALL cancel_order(1042, 7);

-- SQL Server (T-SQL)
EXEC dbo.cancel_order @order_id = 1042, @user_id = 7;

-- PostgreSQL and MySQL: a function returns a value and is used inside a query
SELECT order_total(1042);

आज़माएँ: SQL फ़ॉर्मेटर

Triggers: अपने आप चलने वाला code

Trigger किसी table का होता है और INSERT, UPDATE या DELETE पर fire होता है। BEFORE trigger row लिखे जाने से पहले चलता है और उसे बदल या reject कर सकता है; AFTER trigger बाद में चलता है और audit rows जैसे side effects के लिए सही है। Row-level trigger (FOR EACH ROW) हर बदली row के लिए एक बार चलता है और OLD व NEW values पढ़ सकता है; statement-level trigger हर statement के लिए एक बार चलता है। SQL Server में BEFORE triggers नहीं होते, सिर्फ़ AFTER और INSTEAD OF; उसके triggers हर statement पर एक बार चलते हैं और सारी बदली rows inserted और deleted tables में देखते हैं, इसलिए एक row मानकर लिखा code bulk update पर टूट जाता है।

Transactional engine में trigger के अंदर की error उसे fire करने वाले statement को fail करती है और उसके changes roll back करती है। Triggers कुछ paths छोड़ भी देते हैं: PostgreSQL, MySQL और SQL Server में TRUNCATE DELETE triggers fire नहीं करता, और MySQL में foreign key cascades कोई trigger fire नहीं करते। PostgreSQL में trigger के दो हिस्से होते हैं, जैसे नीचे: एक function जो RETURNS trigger है, और एक CREATE TRIGGER statement जो उसे table से जोड़ता है; इसकी WHEN condition IS DISTINCT FROM इस्तेमाल करती है, यानी not-equal comparison जो NULL को भी सही से संभालता है। इस audit trigger के लिए टेस्ट करें कि वह:

• Table बदलने वाले हर path पर fire हो: API, admin screen, bulk script, import।
• Status को उसकी मौजूदा value पर सेट करने पर कोई row न लिखे।
• हर बदली row के लिए एक बार fire हो, तब भी जब एक statement कई rows बदले।
• बदलाव करने वाले इंसान को record करे: shared application connection में database user application का account होता है, इसलिए user को pass करना पड़ता है, जैसे यहाँ updated_by।

-- PostgreSQL: one audit row whenever an order's status really changes
CREATE FUNCTION log_status_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO order_audit (order_id, old_status, new_status, changed_by, changed_at)
  VALUES (NEW.order_id, OLD.status, NEW.status, NEW.updated_by, now());
  RETURN NEW;
END $$;

CREATE TRIGGER orders_status_audit
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION log_status_change();

Views: saved queries जिन पर reports भरोसा करती हैं

View data नहीं, query store करता है: हर बार पढ़े जाने पर database मौजूदा base tables पर query चलाता है। Views reports और APIs को stable shape देते हैं और सिर्फ़ कुछ columns दिखा सकते हैं, जैसे phone numbers के बिना customer data। Materialized view अपना result store करता है और आख़िरी refresh के समय का data दिखाता है, इसलिए उसके tests refresh को भी cover करते हैं।

View के defects query के defects हैं: गलत join type rows गिरा देता है, child table से join rows को कई गुना कर देता है, filter छूट जाता है, NULLs counts से ग़ायब हो जाते हैं। नीचे वाला view order_items से join करता है, इसलिए दो items वाला order दो बार गिना जाता है।

• View को base tables पर independent query से मिलाएँ, यही आपका test oracle है।
• Edge rows seed करें: कई items वाला order, cancelled order, बिना orders वाला दिन, NULL amount।
• Columns भी जाँचें: छिपाया गया personal data किसी और नाम से वापस न आए।
• Materialized view में refresh से पहले और बाद का result मिलाएँ।

CREATE VIEW daily_sales AS
SELECT o.order_date, COUNT(*) AS order_count, SUM(o.total) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
 WHERE o.status <> 'CANCELLED'
 GROUP BY o.order_date;

-- Seed: one order on 2026-09-14, total 190.00, with two item rows
-- Expected: order_count = 1, revenue = 190.00
-- Actual:   order_count = 2, revenue = 380.00

आज़माएँ: SQL क्वेरी वैलिडेटर

Stored logic की testing: एक worked example

हर routine के contract से tests निकालें: parameters के लिए partitions और boundary values, जहाँ conditions मिलें वहाँ decision table। Procedure को सीधे call करना ISTQB की भाषा में component testing है; उसे इस्तेमाल करने वाली API से call करना integration भी टेस्ट करता है। दोनों करें: application अलग values भेज सकता है या साफ़ database error को उलझाने वाली error के पीछे छिपा सकता है।

हर test, test database में arrange, act, assert है: जानी-पहचानी rows seed करें, call करें, फिर हर उस table को query करें जिसे बदलना चाहिए और जिसे नहीं बदलना चाहिए। अगर procedure ख़ुद commit नहीं करती, तो ROLLBACK से ख़त्म करना शुरुआती state लौटा देता है; वरना ऐसा database इस्तेमाल करें जिसे reset किया जा सके। pgTAP (PostgreSQL) और tSQLt (SQL Server) जैसे frameworks यह pattern automate करते हैं।

Requirement: "Order सिर्फ़ PENDING या PAID रहते हुए cancel हो सकता है। Cancel करने से status CANCELLED होता है और हर item stock में लौटता है; PAID order को उसके total का एक refund भी मिलता है। बाक़ी हालात में call ORDER_NOT_CANCELLABLE (या ORDER_NOT_FOUND) raise करती है और कुछ नहीं बदलता।"

• PAID order 1042 (product 11 की 2 units, product 12 की 1, total 190.00): CANCELLED, stock 50 → 52 और 8 → 9, 190.00 का एक refund, user 7 के नाम एक audit row PAID → CANCELLED।
• PENDING order: cancel, stock वापस, एक audit row PENDING → CANCELLED, कोई refund row नहीं।
• SHIPPED order 1043, उसी order का दूसरा cancel, न मिलने वाला order 9999: error, चारों tables बिना बदले, stock कभी दो बार नहीं लौटता।
• एक ही product की दो lines, अगर schema इजाज़त दे: stock दोनों quantities से बढ़े। जो procedure हर product की quantities पहले जोड़े बिना PostgreSQL के एक UPDATE … FROM order_items से stock लौटाती है, वह दोनों lines में से सिर्फ़ एक लागू करेगी, क्योंकि कई joined rows से match करने वाली target row एक ही बार update होती है।
• बीच में failure: अगर refunds.order_id पर unique key है, तो order 1042 के लिए एक refund seed करें ताकि आख़िरी step, यानी refund का insert, reject हो, और application की तरह बिना wrapping transaction के call करें। Status, stock और audit बिना बदले रहने चाहिए।
• API के ज़रिए: वही rows, और shipped order के लिए साफ़ 4xx, raw database error वाला 500 नहीं।

BEGIN;
-- Arrange: known rows (customer 317 exists; other columns use defaults)
INSERT INTO products (product_id, stock) VALUES (11, 50), (12, 8);
INSERT INTO orders (order_id, customer_id, status, total)
VALUES (1042, 317, 'PAID', 190.00);
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1042, 11, 2), (1042, 12, 1);

-- Act
CALL cancel_order(1042, 7);

-- Assert: every table the procedure and its trigger touch
SELECT status FROM orders WHERE order_id = 1042;             -- CANCELLED
SELECT product_id, stock FROM products ORDER BY product_id;  -- 11: 52, 12: 9
SELECT amount FROM refunds WHERE order_id = 1042;            -- 1 row: 190.00
SELECT old_status, new_status, changed_by
  FROM order_audit WHERE order_id = 1042;                    -- 1 row: PAID, CANCELLED, 7
ROLLBACK;

आज़माएँ: SQL INSERT जनरेटर आज़माएँ: डिसीज़न टेबल जनरेटर

अक्सर पूछे जाने वाले सवाल

संदर्भ

• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development GroupPostgreSQL में SQL की लेक्सिकल संरचना: आइडेंटिफ़ायर, क्वोटिंग, स्ट्रिंग कॉन्स्टेंट और एस्केपिंग।
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023अंतरराष्ट्रीय SQL मानक का framework भाग, जिसे PostgreSQL, MySQL, SQLite आदि की बोलियाँ अलग-अलग हद तक लागू करती हैं।
• ISTQB Certified Tester Foundation Level (CTFL) v4.0ISTQB · 2023सॉफ़्टवेयर टेस्टिंग का फ़ाउंडेशन पाठ्यक्रम: टेस्ट स्तर और प्रकार, टेस्ट डिज़ाइन तकनीकें, टेस्ट प्रबंधन और टूल।
• Transactions — PostgreSQL DocumentationPostgreSQL Global Development Grouptransaction क्या है: कई क़दम जो BEGIN, COMMIT और ROLLBACK से सब-या-कुछ-नहीं लागू होते हैं, commit होने तक दूसरों को नहीं दिखते।