Триггерлерге мысал

1. Триггерлер

2.

Students
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT
);
Products
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(10,2)
);

3.

Orders
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
quantity INT,
price DECIMAL(10,2),
total_price DECIMAL(10,2)
);

4.

Employees
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2)
);
Archive table
CREATE TABLE archive_employees (
id INT,
name VARCHAR(50),
salary DECIMAL(10,2),
deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

5.

Users
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50)
);
Password log
CREATE TABLE change_log (
user_id INT,
old_password VARCHAR(50),
new_password VARCHAR(50),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

6.

Store (дүкен)
CREATE TABLE products_store (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
stock INT
);
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
quantity INT
);
CREATE TABLE log_table (
message VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

7.

8. 2. Триггерлер құру

1) age < 0 болса → 0
CREATE TRIGGER trg_students_age
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
IF NEW.age < 0 THEN
SET NEW.age = 0;
END IF;
END;

9.

2) price NULL болса → 0
CREATE TRIGGER trg_products_price
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
IF NEW.price IS NULL THEN
SET NEW.price = 0;
END IF;
END;

10.

3) total_price автоматты есептеу
CREATE TRIGGER trg_orders_total
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
SET NEW.total_price = NEW.quantity * NEW.price;
END;

11.

4) Жалақы 2 еседен артық өссе → қате
CREATE TRIGGER trg_salary_check
BEFORE UPDATE ON employees
FOR EACH ROWBEGIN
IF NEW.salary > OLD.salary * 2
THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary increase too high!';
END IF;
END;

12.

5) DELETE → архивке сақтау
CREATE TRIGGER trg_archive_employees
AFTER DELETE ON employees
FOR EACH ROW
BEGIN INSERT INTO archive_employees(id, name, salary)
VALUES (OLD.id, OLD.name, OLD.salary);
END;

13.

6) Пароль өзгерсе → логқа жазу
CREATE TRIGGER trg_password_change
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.password <> NEW.password
THEN INSERT INTO change_log(user_id, old_password,
new_password)
VALUES (OLD.id, OLD.password, NEW.password);
END IF;
END;

14.

6) Пароль өзгерсе → логқа жазу
CREATE TRIGGER trg_password_change
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.password <> NEW.password
THEN INSERT INTO change_log(user_id, old_password,
new_password)
VALUES (OLD.id, OLD.password, NEW.password);
END IF;
END;

15.

7) Қорды азайту + лог жазу
CREATE TRIGGER trg_sales_after_insert
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
Қорды азайту
UPDATE products_store
SET stock = stock - NEW.quantity
WHERE id = NEW.product_id;
Лог жазу
INSERT INTO log_table(message)
VALUES (CONCAT('Product ', NEW.product_id, ' sold: ', NEW.quantity));
END;

16.

8) Қор 0 болса → ескерту
CREATE TRIGGER trg_stock_check
AFTER UPDATE ON products_store
FOR EACH ROW
BEGIN
IF NEW.stock = 0 THEN
INSERT INTO log_table(message)
VALUES (CONCAT('Product ', NEW.id, ' is out of stock!'));
END IF;
END;

17.

Тексеруге мысалдар
-- students
INSERT INTO students(name, age) VALUES ('Ali', -5);
-- products
INSERT INTO products(name, price) VALUES ('Phone', NULL);
-- orders
INSERT INTO orders(product_id, quantity, price)
VALUES (1, 2, 100);
-- employees
UPDATE employees SET salary = 10000 WHERE id = 1;
-- users
UPDATE users SET password = 'newpass' WHERE id = 1;
-- sales
INSERT INTO sales(product_id, quantity) VALUES (1, 3);