Function ও Trigger

Stored Function

Function একটি value return করে, SELECT-এ সরাসরি ব্যবহার করা যায়। SP multiple result দিতে পারে, Function পারে না।

Function তৈরি

DELIMITER $$

CREATE FUNCTION CalculateTax(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * 0.15;
END$$

DELIMITER ;

-- SELECT-এ ব্যবহার
SELECT name, price, CalculateTax(price) AS tax
FROM products;

জটিল Function

DELIMITER $$

CREATE FUNCTION GetDiscount(price DECIMAL(10,2))
RETURNS DECIMAL(3,2)
DETERMINISTIC
BEGIN
    DECLARE rate DECIMAL(3,2);

    IF price >= 1000 THEN SET rate = 0.20;
    ELSEIF price >= 500 THEN SET rate = 0.10;
    ELSE SET rate = 0;
    END IF;

    RETURN rate;
END$$
DELIMITER ;

SELECT name, price,
    price * GetDiscount(price) AS discount,
    price * (1 - GetDiscount(price)) AS final_price
FROM products;

Function Keywords

• DETERMINISTIC — same input → same output
• READS SQL DATA — SELECT করে কিন্তু modify নয়
• MODIFIES SQL DATA — পরিবর্তন করে
• NO SQL — SQL ব্যবহার করে না

Trigger কী?

Trigger হলো স্বয়ংক্রিয়ভাবে চালিত SP — INSERT/UPDATE/DELETE ঘটলে নিজে নিজেই চলে।

Trigger Types

• BEFORE INSERT / AFTER INSERT
• BEFORE UPDATE / AFTER UPDATE
• BEFORE DELETE / AFTER DELETE

BEFORE INSERT Trigger

DELIMITER $$

CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    -- Email lowercase
    SET NEW.email = LOWER(NEW.email);

    -- Default role
    IF NEW.role IS NULL THEN
        SET NEW.role = 'user';
    END IF;

    -- Validation
    IF NEW.age < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Age ঋণাত্মক নয়';
    END IF;
END$$
DELIMITER ;

AFTER INSERT (Audit Log)

DELIMITER $$

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, table_name, record_id, created_at)
    VALUES ('INSERT', 'users', NEW.id, NOW());
END$$
DELIMITER ;

AFTER UPDATE (পরিবর্তন track)

DELIMITER $$

CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.email != NEW.email THEN
        INSERT INTO email_history
        (user_id, old_email, new_email, changed_at)
        VALUES (NEW.id, OLD.email, NEW.email, NOW());
    END IF;
END$$
DELIMITER ;

AFTER DELETE (Backup)

DELIMITER $$

CREATE TRIGGER after_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO deleted_users
    (id, name, email, deleted_at)
    VALUES (OLD.id, OLD.name, OLD.email, NOW());
END$$
DELIMITER ;

Stock Update Trigger (বাস্তব)

-- Order item insert হলে product stock কমাও
DELIMITER $$
CREATE TRIGGER reduce_stock
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;
END$$
DELIMITER ;

Trigger Management

SHOW TRIGGERS;
SHOW TRIGGERS FROM shop_db;
SHOW CREATE TRIGGER before_user_insert;
DROP TRIGGER IF EXISTS before_user_insert;

NEW vs OLD

• NEW — INSERT/UPDATE-এ নতুন মান
• OLD — UPDATE/DELETE-এ পুরনো মান
• INSERT-এ শুধু NEW, DELETE-এ শুধু OLD

⚠️ সাবধানতা

• Trigger debug কঠিন — hidden side effect
• Performance প্রভাবিত করে
• Recursive trigger বিপজ্জনক
• বড় প্রজেক্টে application layer-এ logic রাখা ভালো