October 1, 2026

MySQL Views, Stored Procedures & Triggers – Advanced Database Features

Complete Guide for Beginners with Practical Examples

Note: Is blog ko beginner-friendly rakha gaya hai, lekin examples real-world PHP/CodeIgniter 4 projects ko dhyan me rakhkar diye gaye hain.

---

1. Introduction

Jab hum MySQL ke basics seekhte hain, tab hum mostly:

• SELECT
• INSERT
• UPDATE
• DELETE
• WHERE
• JOIN
• GROUP BY
• TRANSACTION

jaise concepts use karte hain.

Lekin real-world applications me database sirf data store karne ke liye nahi hota.

Kabhi hume:

• complex query ko reusable banana hota hai
• database ke andar business logic rakhna hota hai
• kisi event par automatically action perform karwana hota hai
• audit/history maintain karni hoti hai
• application code me repeated SQL queries ko reduce karna hota hai

Yahin par Views, Stored Procedures aur Triggers useful hote hain.

Is tutorial me hum teeno concepts ko practical examples ke saath samjhenge.

---

2. Views, Stored Procedures aur Triggers Kya Hote Hain?

Simple language me:

Feature	Simple Meaning
View	Reusable SELECT query / virtual table
Stored Procedure	Database ke andar stored reusable program/routine
Trigger	Table event hone par automatically execute hone wala database routine

Ek simple example:

VIEW
↓
Complex SELECT ko reusable bana do

STORED PROCEDURE
↓
Business operation ko database routine me store karo

TRIGGER
↓
INSERT/UPDATE/DELETE hone par automatic action

---

3. MySQL View Kya Hai?

View ek virtual table hoti hai jo kisi SELECT query ke result par based hoti hai.

Example:

Suppose hamare paas customers table hai:

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(150),
    city VARCHAR(100)
);

Aur orders table:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    total_amount DECIMAL(10,2),
    order_status VARCHAR(50)
);

Ab hume baar-baar customer ke saath order information retrieve karni hai.

Instead of repeatedly writing:

SELECT 
    c.id,
    c.name,
    c.email,
    o.total_amount,
    o.order_status
FROM customers c
JOIN orders o 
    ON c.id = o.customer_id;

Hum iska View bana sakte hain.

---

4. CREATE VIEW Syntax

Basic syntax:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

Example:

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE city = 'Raipur';

Ab:

SELECT * FROM active_customers;

execute kar sakte hain.

Yani complex SELECT query ko ek reusable database object mil gaya.

---

5. View Example – Customer Orders

Let's create a practical View:

CREATE VIEW customer_orders AS
SELECT
    c.id AS customer_id,
    c.name AS customer_name,
    c.email,
    o.id AS order_id,
    o.total_amount,
    o.order_status
FROM customers c
JOIN orders o
    ON c.id = o.customer_id;

Ab query:

SELECT * 
FROM customer_orders;

Example output:

customer_id	customer_name	email	order_id	total_amount	order_status
1	Rahul	rahul@example.com	101	2500.00	Paid
2	Amit	amit@example.com	102	1800.00	Pending
3	Neha	neha@example.com	103	4200.00	Paid

Ab application me har baar lengthy JOIN likhne ki zarurat nahi.

---

6. View ka Real-World Use

Views ka use commonly hota hai:

• Reporting
• Admin dashboards
• Customer reports
• Sales reports
• Employee reports
• Complex JOIN queries
• Sensitive columns hide karne ke liye
• Reusable database queries

Example:

Suppose employees table me:

id
name
email
salary
bank_account
password_hash

Lekin reporting user ko sirf:

id
name
email

dikhana hai.

Aap ek View bana sakte hain:

CREATE VIEW employee_public_data AS
SELECT
    id,
    name,
    email
FROM employees;

Ab:

SELECT * FROM employee_public_data;

se limited columns expose kiye ja sakte hain.

Important: View ko security boundary samajhne se pehle proper MySQL privileges bhi configure karne chahiye.

---

7. View ko Modify Kaise Karein?

Agar existing View ko modify karna ho:

CREATE OR REPLACE VIEW customer_orders AS
SELECT
    c.id AS customer_id,
    c.name AS customer_name,
    o.id AS order_id,
    o.total_amount
FROM customers c
JOIN orders o
    ON c.id = o.customer_id;

CREATE OR REPLACE VIEW existing View ko replace kar deta hai.

---

8. View ko Delete Kaise Karein?

View remove karne ke liye:

DROP VIEW customer_orders;

Safer form:

DROP VIEW IF EXISTS customer_orders;

---

9. View ke Important Points

View ke baare me kuch important concepts:

View physical table nahi hoti

Generally View stored query definition hoti hai; data underlying tables se retrieve hota hai.

customers
     +
orders
     ↓
   VIEW
     ↓
customer_orders

Har View automatically updatable nahi hoti

Simple views kuch cases me updateable ho sakti hain, lekin complex JOIN, aggregation, GROUP BY, DISTINCT etc. wali views ke update rules restrictive ho sakte hain.

Isliye beginner level par View ko primarily reusable query/reporting abstraction ke roop me samajhna best hai.

---

10. View vs Table

Feature	Table	View
Actual data store	Yes	Usually no
SELECT possible	Yes	Yes
Complex query reuse	Limited	Excellent
JOIN encapsulation	No	Yes
Physical storage	Data stored	Query definition
DROP possible	Yes	Yes
Automatically updatable	Depends	Depends on View definition

---

11. Stored Procedure Kya Hai?

Ab aate hain second important feature par:

Stored Procedure

Stored Procedure ek named database routine hai jise explicitly call karke execute kiya ja sakta hai.

Simple example:

Application
     ↓
CALL procedure
     ↓
MySQL Procedure
     ↓
Multiple SQL statements
     ↓
Result

Suppose har baar customer ka order create karne ke liye multiple SQL operations karne padte hain.

Aap un operations ko ek Stored Procedure me encapsulate kar sakte hain.

---

12. Stored Procedure ka Basic Syntax

Basic syntax:

DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN

    SQL statements;

END //

DELIMITER ;

Example:

DELIMITER //

CREATE PROCEDURE get_all_customers()
BEGIN

    SELECT *
    FROM customers;

END //

DELIMITER ;

Ab procedure call karne ke liye:

CALL get_all_customers();

---

13. DELIMITER Kya Hai?

Beginner ke liye ye important point hai.

Aapko code me:

DELIMITER //

dikhai dega.

DELIMITER MySQL client/tool ka statement terminator change karne ka command hai.

Normally:

;

statement end karta hai.

Procedure ke andar multiple statements hain:

BEGIN
    SELECT ...;
    UPDATE ...;
END

Isliye MySQL client me temporary delimiter change karna convenient hota hai.

Example:

DELIMITER //

CREATE PROCEDURE test_procedure()
BEGIN
    SELECT 'Hello MySQL';
END //

DELIMITER ;

Important: DELIMITER server-side stored procedure ka part nahi hai; ye client/tool instruction hai.

---

14. Stored Procedure with Parameter

Procedure ko parameter bhi diya ja sakta hai.

Example:

DELIMITER //

CREATE PROCEDURE get_customer_by_id(IN customerId INT)
BEGIN

    SELECT *
    FROM customers
    WHERE id = customerId;

END //

DELIMITER ;

Call:

CALL get_customer_by_id(5);

Yahan:

5
↓
customerId
↓
WHERE id = customerId

---

15. IN, OUT aur INOUT Parameters

Stored Procedures me commonly teen parameter modes milte hain:

Parameter	Meaning
IN	Input value
OUT	Procedure output
INOUT	Input + output

---

16. IN Parameter Example

DELIMITER //

CREATE PROCEDURE get_orders_by_customer(IN customerId INT)
BEGIN

    SELECT *
    FROM orders
    WHERE customer_id = customerId;

END //

DELIMITER ;

Call:

CALL get_orders_by_customer(10);

---

17. OUT Parameter Example

Suppose hume customer ke total orders count karne hain.

DELIMITER //

CREATE PROCEDURE count_customer_orders(
    IN customerId INT,
    OUT totalOrders INT
)
BEGIN

    SELECT COUNT(*)
    INTO totalOrders
    FROM orders
    WHERE customer_id = customerId;

END //

DELIMITER ;

Call:

CALL count_customer_orders(10, @total);

Result check:

SELECT @total;

Example:

@total
------
7

---

18. Stored Procedure with IF

Stored Procedure ke andar conditional logic bhi use kar sakte hain.

Example:

DELIMITER //

CREATE PROCEDURE check_order_amount(IN orderAmount DECIMAL(10,2))
BEGIN

    IF orderAmount >= 5000 THEN

        SELECT 'High Value Order' AS message;

    ELSE

        SELECT 'Regular Order' AS message;

    END IF;

END //

DELIMITER ;

Call:

CALL check_order_amount(7500);

Output:

High Value Order

---

19. Stored Procedure with Multiple SQL Statements

Real-world example:

DELIMITER //

CREATE PROCEDURE create_customer(
    IN customerName VARCHAR(100),
    IN customerEmail VARCHAR(150),
    IN customerCity VARCHAR(100)
)
BEGIN

    INSERT INTO customers (
        name,
        email,
        city
    )
    VALUES (
        customerName,
        customerEmail,
        customerCity
    );

END //

DELIMITER ;

Call:

CALL create_customer(
    'Rahul Sharma',
    'rahul@example.com',
    'Raipur'
);

---

20. Stored Procedure ke Real-World Use Cases

Stored Procedures ka use ho sakta hai:

• complex reporting
• repeated database operations
• batch operations
• database-side calculations
• controlled data access
• administrative operations
• business workflows
• scheduled database tasks

Lekin har business logic ko Stored Procedure me daal dena automatically better architecture nahi hota.

Application architecture, portability, testing aur maintainability ko consider karna chahiye.

---

21. Trigger Kya Hai?

Ab third feature:

Trigger

Trigger ek database routine hai jo table ke specific event ke response me automatically execute hota hai.

Difference dekhiye:

Stored Procedure
↓
CALL karna padta hai

Trigger
↓
Event hone par automatically execute

For example:

UPDATE employee
       ↓
AFTER UPDATE Trigger
       ↓
Audit table me log

Application ko separately audit insert karne ki zarurat nahi pad sakti.

---

22. MySQL Trigger Events

MySQL me triggers commonly in events ke saath create kiye ja sakte hain:

INSERT

BEFORE INSERT
AFTER INSERT

UPDATE

BEFORE UPDATE
AFTER UPDATE

DELETE

BEFORE DELETE
AFTER DELETE

Yani:

BEFORE + INSERT
AFTER  + INSERT

BEFORE + UPDATE
AFTER  + UPDATE

BEFORE + DELETE
AFTER  + DELETE

---

23. Trigger ka Basic Syntax

DELIMITER //

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN

    SQL statements;

END //

DELIMITER ;

---

24. AFTER INSERT Trigger Example

Suppose hamare paas audit table hai:

CREATE TABLE customer_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    action VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ab customer insert hone par automatically log create karna hai.

DELIMITER //

CREATE TRIGGER after_customer_insert
AFTER INSERT ON customers
FOR EACH ROW
BEGIN

    INSERT INTO customer_logs (
        customer_id,
        action
    )
    VALUES (
        NEW.id,
        'Customer Created'
    );

END //

DELIMITER ;

Ab:

INSERT INTO customers (
    name,
    email,
    city
)
VALUES (
    'Amit',
    'amit@example.com',
    'Raipur'
);

Automatically:

customers
    ↓
INSERT
    ↓
Trigger
    ↓
customer_logs

me record create hoga.

---

25. NEW aur OLD Kya Hote Hain?

Triggers me NEW aur OLD bahut important hain.

Event	NEW	OLD
INSERT	Yes	No
UPDATE	Yes	Yes
DELETE	No	Yes

Example:

NEW.name

means newly inserted/updated value.

Aur:

OLD.name

means previous value.

---

26. UPDATE Trigger Example

Suppose employee salary changes hone par audit record maintain karna hai.

Audit table:

CREATE TABLE salary_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Trigger:

DELIMITER //

CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN

    IF OLD.salary <> NEW.salary THEN

        INSERT INTO salary_logs (
            employee_id,
            old_salary,
            new_salary
        )
        VALUES (
            OLD.id,
            OLD.salary,
            NEW.salary
        );

    END IF;

END //

DELIMITER ;

Ab agar:

UPDATE employees
SET salary = 60000
WHERE id = 5;

to audit table me old aur new salary store ho sakti hai.

---

27. DELETE Trigger Example

Suppose deleted customer ka audit maintain karna hai.

CREATE TABLE deleted_customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    customer_name VARCHAR(100),
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Trigger:

DELIMITER //

CREATE TRIGGER after_customer_delete
AFTER DELETE ON customers
FOR EACH ROW
BEGIN

    INSERT INTO deleted_customers (
        customer_id,
        customer_name
    )
    VALUES (
        OLD.id,
        OLD.name
    );

END //

DELIMITER ;

Ab:

DELETE FROM customers
WHERE id = 10;

ke baad deleted customer ki information audit/archive table me save ho sakti hai.

---

28. BEFORE Trigger Kab Use Karein?

BEFORE trigger ka use event se pehle validation ya allowed value adjustment jaise cases me kiya ja sakta hai.

Example:

DELIMITER //

CREATE TRIGGER before_customer_insert
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN

    SET NEW.name = TRIM(NEW.name);

END //

DELIMITER ;

Agar input:

"   Rahul Sharma   "

hai, to NEW.name ko trim karke store karne ka logic apply ho sakta hai.

---

29. BEFORE vs AFTER Trigger

Feature	BEFORE	AFTER
Event se pehle execute	Yes	No
Event ke baad execute	No	Yes
Validation/useful transformations	Common use	Less suitable
Audit logging	Possible, but context-dependent	Common use
NEW values	Read; appropriate cases me modify	Read-only

Beginner rule:

BEFORE
↓
Data process/validate/adjust karna

AFTER
↓
Event ke baad related action/logging

---

30. Trigger ka Real-World Example – Order Audit

Suppose:

orders

table me order status hai:

Pending
Processing
Shipped
Delivered
Cancelled

Status change hone par audit history maintain karni hai.

Audit table:

CREATE TABLE order_status_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    old_status VARCHAR(50),
    new_status VARCHAR(50),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Trigger:

DELIMITER //

CREATE TRIGGER after_order_status_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN

    IF OLD.order_status <> NEW.order_status THEN

        INSERT INTO order_status_history (
            order_id,
            old_status,
            new_status
        )
        VALUES (
            OLD.id,
            OLD.order_status,
            NEW.order_status
        );

    END IF;

END //

DELIMITER ;

Ab:

UPDATE orders
SET order_status = 'Shipped'
WHERE id = 101;

Automatically history create ho sakti hai.

---

31. View + Procedure + Trigger Ek Saath

Real-world application me teeno features ek saath bhi use ho sakte hain.

Example:

Customer Order System

View

Order reporting:

customers + orders
        ↓
customer_order_report

Stored Procedure

Order creation:

CALL create_order(...)
        ↓
Order processing

Trigger

Order status history:

UPDATE orders
        ↓
Trigger
        ↓
order_status_history

Architecture:

              MySQL Database
                    |
       +------------+------------+
       |            |            |
      VIEW       PROCEDURE     TRIGGER
       |            |            |
    Reports      Operations     Audit

---

32. View vs Stored Procedure vs Trigger

Ye interview aur practical development dono ke liye important comparison hai.

Feature	View	Stored Procedure	Trigger
Main purpose	Query abstraction	Reusable operation	Automatic event action
Execute kaise hota hai?	SELECT	CALL	Automatically
Parameters	No procedure-style parameters	Yes	No direct call parameters
Triggered automatically	No	No	Yes
Mainly SELECT/reporting	Yes	Not limited	No
Business logic	Limited	Yes	Yes, but carefully
Audit logging	Indirect	Possible	Common
Complex SQL reuse	Excellent	Excellent	Event-specific

Simple formula:

VIEW
= Reusable Query

PROCEDURE
= Reusable Database Program

TRIGGER
= Automatic Database Action

---

33. View vs Stored Procedure – Simple Example

Suppose:

SELECT *
FROM orders
WHERE order_status = 'Pending';

Agar hume ise reusable query banana hai:

CREATE VIEW pending_orders AS
SELECT *
FROM orders
WHERE order_status = 'Pending';

Then:

SELECT * FROM pending_orders;

Lekin agar hume parameter ke basis par operation perform karna hai:

customer ID
↓
orders find
↓
calculation
↓
additional operation

Stored Procedure more appropriate ho sakti hai.

---

34. Trigger vs Stored Procedure

Important difference:

Stored Procedure

Developer explicitly call karta hai:

CALL update_customer();

Trigger

Developer trigger call nahi karta.

Example:

UPDATE customers
SET city = 'Raipur'
WHERE id = 5;

Agar matching trigger configured hai, wo automatically execute hoga.

UPDATE
  ↓
Trigger
  ↓
Automatic Action

---

35. MySQL Trigger List Kaise Dekhein?

Existing triggers dekhne ke liye:

SHOW TRIGGERS;

Specific database ke liye:

SHOW TRIGGERS FROM my_database;

---

36. Trigger Delete Kaise Karein?

DROP TRIGGER trigger_name;

Example:

DROP TRIGGER after_customer_insert;

Safer:

DROP TRIGGER IF EXISTS after_customer_insert;

---

37. Stored Procedure List Kaise Dekhein?

SHOW PROCEDURE STATUS;

Specific database:

SHOW PROCEDURE STATUS
WHERE Db = 'my_database';

Procedure definition inspect karne ke liye:

SHOW CREATE PROCEDURE get_all_customers;

---

38. Stored Procedure Delete Kaise Karein?

DROP PROCEDURE IF EXISTS get_all_customers;

---

39. View List Kaise Dekhein?

SHOW FULL TABLES
WHERE Table_type = 'VIEW';

Ya:

SHOW TABLES;

ke output me Views bhi appear ho sakti hain, depending on context/tool.

---

40. View Definition Kaise Dekhein?

SHOW CREATE VIEW customer_orders;

Isse View ki definition inspect kar sakte hain.

---

41. PHP me View Use Karna

Agar View already database me available hai:

CREATE VIEW customer_orders AS
SELECT
    c.name,
    o.total_amount,
    o.order_status
FROM customers c
JOIN orders o
    ON c.id = o.customer_id;

PHP application me simply query:

$sql = "SELECT * FROM customer_orders";

$result = $db->query($sql);

View application ke liye table jaisi queryable interface provide kar sakti hai.

---

42. CodeIgniter 4 me View Use Karna

CodeIgniter 4 me:

$db = \Config\Database::connect();

$query = $db->query("
    SELECT *
    FROM customer_orders
");

$orders = $query->getResult();

Yahan:

customer_orders

MySQL View hai.

---

43. CodeIgniter 4 me Stored Procedure Call Karna

Suppose procedure hai:

CREATE PROCEDURE get_customer_by_id(IN customerId INT)
BEGIN
    SELECT *
    FROM customers
    WHERE id = customerId;
END

CodeIgniter 4:

$db = \Config\Database::connect();

$query = $db->query(
    "CALL get_customer_by_id(?)",
    [$customerId]
);

$result = $query->getResult();

Prepared parameters use karna application code me safer practice hai.

---

44. CodeIgniter 4 me Trigger

Trigger ko application se normally:

CALL trigger_name();

nahi kiya jata.

Trigger database event se automatically execute hota hai.

Example:

$db->query("
    UPDATE orders
    SET order_status = ?
    WHERE id = ?
", [$status, $orderId]);

Agar database me matching AFTER UPDATE trigger hai:

CodeIgniter
    ↓
UPDATE orders
    ↓
MySQL
    ↓
Trigger automatically
    ↓
Audit table

---

45. Triggers ke Advantages

Triggers useful ho sakte hain because:

1. Automatic execution

Application ko manually trigger action call nahi karna padta.

2. Centralized database behavior

Agar multiple applications same database use karti hain, database-level behavior consistent rakha ja sakta hai.

3. Audit logging

Changes ka history maintain karne me useful.

4. Data-related automation

Certain database events ke response me automatic actions possible hain.

---

46. Triggers ke Disadvantages

Triggers ko overuse nahi karna chahiye.

Reason:

Hidden logic

Developer:

UPDATE orders ...

dekhta hai.

Lekin background me trigger:

UPDATE
 ↓
Trigger 1
 ↓
Trigger-related action

ho sakta hai.

Isse debugging difficult ho sakti hai.

Testing complexity

Application behavior aur database behavior dono test karne padte hain.

Unexpected side effects

Ek simple INSERT ke multiple database effects ho sakte hain.

Isliye triggers ka use carefully and intentionally karna chahiye.

---

47. Stored Procedures ke Advantages

Stored Procedures ke benefits:

• SQL logic centralize kar sakte hain
• repeated SQL reduce ho sakta hai
• complex database operations encapsulate kar sakte hain
• permissions ko controlled manner me design kiya ja sakta hai
• database-side processing useful ho sakti hai

---

48. Stored Procedures ke Disadvantages

Potential drawbacks:

• application aur database tightly coupled ho sakte hain
• version control/deployment management more complex ho sakta hai
• debugging application code ke comparison me different ho sakti hai
• database vendor portability reduce ho sakti hai
• excessive business logic database me rakhne se architecture complex ho sakta hai

---

49. Views ke Advantages

Views useful hain for:

Complex JOIN
     ↓
Reusable View
     ↓
Simple SELECT

Benefits:

• query complexity hide
• reporting easy
• reusable SQL
• selected columns expose karna
• application queries simplify karna

---

50. Views ke Limitations

Views:

• actual table ka replacement nahi hain
• har View updateable nahi hoti
• complex View definitions maintain karna difficult ho sakta hai
• underlying tables/indexes ka performance impact still matter karta hai

View use karne ka matlab ye nahi ki underlying query automatically fast ho gayi.

---

51. Common Beginner Mistakes

Mistake 1: View ko actual table samajhna

View ≠ Normal physical table

View generally query abstraction provide karti hai.

---

Mistake 2: Har logic ke liye Trigger banana

Har operation ke liye trigger banana database ko difficult-to-understand bana sakta hai.

---

Mistake 3: Procedure me unnecessary complexity

Agar simple:

SELECT * FROM customers;

hai, to sirf iske liye complex procedure create karna unnecessary ho sakta hai.

---

Mistake 4: NEW aur OLD ko incorrectly use karna

Remember:

INSERT → NEW
UPDATE → OLD + NEW
DELETE → OLD

---

Mistake 5: Trigger ko manually call karna

Trigger:

CALL trigger_name();

se normally execute nahi hota.

Table event hone par automatically execute hota hai.

---

Mistake 6: Trigger ke andar transaction control use karna

MySQL triggers ke andar direct transaction-control statements jaise:

COMMIT;
ROLLBACK;

use nahi kiye ja sakte.

Transaction control application/session level transaction ke context me handle karein.

---

52. Best Practices

1. View ka clear naming convention rakhein

Examples:

customer_orders
active_customers
monthly_sales_report
employee_public_data

---

2. Procedure names meaningful rakhein

Bad:

proc1
test2
abc_proc

Better:

get_customer_by_id
create_customer
get_monthly_sales
count_customer_orders

---

3. Trigger names descriptive rakhein

Example:

after_customer_insert
after_order_status_update
before_customer_insert
after_employee_delete

---

4. Triggers ka documentation maintain karein

Database documentation me clearly likhein:

Trigger Name
↓
Table
↓
Event
↓
Purpose
↓
Side Effects

---

5. Long-running operations avoid karein

Database routines ko unnecessarily complicated aur long-running na banayein.

---

6. Business logic carefully place karein

Har business rule ko:

Application

ya:

Database

me blindly put na karein.

Decide karein ki logic ko maintain, test aur deploy karna kahan better rahega.

---

53. Real-World E-Commerce Example

Suppose e-commerce system hai.

Tables:

customers
products
orders
order_items
payments
order_history

Architecture:

View

Customer order report:

customers
   +
orders
   +
order_items
   ↓
customer_order_report

Stored Procedure

Order operation:

CALL create_order(...)

Trigger

Order status update:

UPDATE orders
       ↓
AFTER UPDATE Trigger
       ↓
order_history

Is tarah database me reusable query, explicit routine aur automatic event handling teeno available ho sakte hain.

---

54. Real-World Banking Example

Banking system me transaction safety ke saath database features combine ho sakte hain.

For example:

Transfer request
      ↓
Stored Procedure / Application transaction
      ↓
Debit Account
      ↓
Credit Account
      ↓
Audit information

Audit table ko maintain karne ke liye carefully designed trigger bhi use kiya ja sakta hai.

Lekin financial systems me business logic, authorization, concurrency, transaction boundaries aur audit requirements ko comprehensive architecture ke saath design karna chahiye.

---

55. Real-World Employee Management Example

Suppose employee salary update hoti hai:

UPDATE employees
SET salary = 75000
WHERE id = 10;

Trigger:

UPDATE
 ↓
AFTER UPDATE
 ↓
salary_logs

Result:

employee_id	old_salary	new_salary
10	65000	75000

Ye audit/history system ke liye useful ho sakta hai.

---

56. View + Index Relationship

Ek important misconception:

"View bana diya to query fast ho jayegi."

Aisa automatically nahi hota.

Agar View internally:

SELECT ...
FROM orders
JOIN customers ...
WHERE ...

execute karti hai, to underlying tables ke indexes aur query structure still important hain.

Example indexes:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Aur:

CREATE INDEX idx_orders_status
ON orders(order_status);

Performance ko query execution plan ke through evaluate karna chahiye.

---

57. Stored Procedure vs Application Code

Suppose:

PHP / CodeIgniter

me business logic hai.

Question:

Kya sab kuch Stored Procedure me move kar dena chahiye?

No universal rule.

Decision depend karta hai:

• application architecture
• database sharing
• portability requirements
• testing strategy
• deployment process
• team expertise
• transaction requirements
• performance requirements

Simple CRUD applications me application-level logic often easier to maintain hoti hai, while database routines specific centralized operations me useful ho sakti hain.

---

58. Trigger Kab Use Karna Chahiye?

Trigger consider kar sakte hain jab:

Database event
      ↓
Automatic database-side action

clearly required ho.

Examples:

• audit history
• change tracking
• database-side validation
• derived actions
• automatic logging

But trigger avoid/limit karna better ho sakta hai jab same behavior application layer me clearer, testable aur maintainable ho.

---

59. Important MySQL Commands – Quick Reference

View

CREATE VIEW view_name AS
SELECT ...;

CREATE OR REPLACE VIEW view_name AS
SELECT ...;

DROP VIEW IF EXISTS view_name;

SHOW CREATE VIEW view_name;

---

Stored Procedure

CREATE PROCEDURE procedure_name()
BEGIN
    ...
END;

Call:

CALL procedure_name();

Delete:

DROP PROCEDURE IF EXISTS procedure_name;

Inspect:

SHOW CREATE PROCEDURE procedure_name;

---

Trigger

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
    ...
END;

Delete:

DROP TRIGGER IF EXISTS trigger_name;

List:

SHOW TRIGGERS;

---

60. Quick Revision Table

Concept	Remember This
View	Reusable virtual query
CREATE VIEW	View create karta hai
DROP VIEW	View delete karta hai
Procedure	Explicitly called database routine
CALL	Procedure execute karta hai
IN	Input parameter
OUT	Output parameter
INOUT	Input + output
Trigger	Automatically execute hota hai
BEFORE	Event se pehle
AFTER	Event ke baad
NEW	New row values
OLD	Previous row values
SHOW TRIGGERS	Triggers list
SHOW CREATE VIEW	View definition
SHOW CREATE PROCEDURE	Procedure definition

---

61. Interview Questions

Q1. What is a View?

View ek virtual table/query abstraction hai jo SELECT definition par based hoti hai.

Q2. What is a Stored Procedure?

Stored Procedure database me stored named routine hai jo CALL ke through explicitly execute hoti hai.

Q3. What is a Trigger?

Trigger table event ke response me automatically execute hone wala database routine hai.

Q4. Difference between Procedure and Trigger?

Procedure → manually/explcitly CALL
Trigger   → automatically event par execute

Q5. What are NEW and OLD?

NEW → new row values
OLD → previous row values

Q6. Can every View be updated?

No. View ki definition aur underlying query structure determine karti hai ki wo updateable hai ya nahi.

Q7. Can Trigger use COMMIT?

MySQL trigger ke andar direct COMMIT/ROLLBACK transaction-control statements allowed nahi hain.

---

62. Practice Exercises

Exercise 1 – Create a View

customers aur orders ko join karke:

customer_name
email
order_id
total_amount

show karne wali View create karein.

---

Exercise 2 – Create a Procedure

Aisi procedure banayein:

CALL get_orders_by_status('Pending');

jo Pending orders return kare.

---

Exercise 3 – Create an Audit Trigger

Employee salary update hone par:

employee_id
old_salary
new_salary
changed_at

audit table me insert karein.

---

Exercise 4 – Use NEW and OLD

Ek orders table banayein aur status update hone par old/new status history maintain karein.

---

Exercise 5 – CodeIgniter 4

CodeIgniter 4 se:

CALL get_customer_by_id(?)

execute karein aur result display karein.

---

63. Complete Concept in One Diagram

                    MYSQL DATABASE
                          |
          +---------------+---------------+
          |               |               |
          ↓               ↓               ↓
        VIEW          PROCEDURE         TRIGGER
          |               |               |
     Reusable          Explicit          Automatic
       Query            CALL             Event
          |               |               |
          ↓               ↓               ↓
      Reporting        Operations       Audit/Action

Simple formula:

VIEW
→ Query ko reusable banao

STORED PROCEDURE
→ Database operation ko reusable banao

TRIGGER
→ Database event par automatic action karo

---

64. Final Conclusion

MySQL ke Views, Stored Procedures aur Triggers advanced database development ke important features hain.

Aapko inhe is tarah yaad rakhna chahiye:

VIEW
= Reusable SELECT

PROCEDURE
= Explicitly called database routine

TRIGGER
= Automatically executed database routine

Views complex queries aur reporting ko simplify kar sakti hain.

Stored Procedures repeated database operations ko centralize kar sakti hain.

Triggers database events ke response me automatic actions aur audit logging ke liye useful ho sakte hain.

Lekin advanced features ka matlab ye nahi ki har project me maximum database logic use karna chahiye. Clear architecture, maintainability, testing, performance aur team workflow ko consider karke decide karna chahiye ki kaunsa logic View, Procedure, Trigger ya application layer me hona chahiye.

Agar aap PHP ya CodeIgniter 4 ke saath MySQL use kar rahe hain, to in concepts ko samajhna aapko simple CRUD applications se production-oriented database architecture ki taraf le jaata hai.