SQLize Online / PHPize Online  /  SQLtest Online

-- Таблица для Confectionery CREATE TABLE Confectionery ( title VARCHAR(255), city VARCHAR(255), rating FLOAT, year INT, PRIMARY KEY (title, city) ); INSERT INTO Confectionery VALUES ('Sweet Dreams', 'Moscow', 4.5, 2010); INSERT INTO Confectionery VALUES ('Candy Land', 'Saint-Petersburg', 4.8, 2012); INSERT INTO Confectionery VALUES ('Chocolate World', 'Novosibirsk', 5.0, 2015); INSERT INTO Confectionery VALUES ('Cake Heaven', 'Yekaterinburg', 4.2, 2018); INSERT INTO Confectionery VALUES ('Pastry Palace', 'Kazan', 4.8, 2011); INSERT INTO Confectionery VALUES ('Dessert Oasis', 'Nizhny Novgorod', 4.5, 2019); INSERT INTO Confectionery VALUES ('Candy Castle', 'Samara', 4.0, 2013); INSERT INTO Confectionery VALUES ('Sugar Rush', 'Omsk', 4.8, 2014); INSERT INTO Confectionery VALUES ('Delicacy Depot', 'Kaliningrad', 4.9, 2016); INSERT INTO Confectionery VALUES ('Treat Factory', 'Volgograd', 4.5, 2020); SELECT * FROM Confectionery; -- Таблица для Customer CREATE TABLE Customer ( name VARCHAR(255), phoneNumber VARCHAR(20), PRIMARY KEY (name, phoneNumber) ); INSERT INTO Customer VALUES ('Alexey', '+7 (123) 456-78-90'); INSERT INTO Customer VALUES ('Maria', '+7 (987) 654-32-10'); INSERT INTO Customer VALUES ('Dmitry', '+7 (456) 789-01-23'); INSERT INTO Customer VALUES ('Anna', '+7 (321) 098-76-54'); INSERT INTO Customer VALUES ('Sergei', '+7 (678) 543-21-09'); INSERT INTO Customer VALUES ('Ekaterina', '+7 (234) 765-43-21'); INSERT INTO Customer VALUES ('Ivan', '+7 (890) 123-45-67'); INSERT INTO Customer VALUES ('Olga', '+7 (567) 890-12-34'); INSERT INTO Customer VALUES ('Maksim', '+7 (345) 678-90-12'); INSERT INTO Customer VALUES ('Elena', '+7 (901) 234-56-78'); SELECT * FROM Customer; -- Таблица для Employee CREATE TABLE Employee ( name VARCHAR(255), post VARCHAR(255), phoneNumber VARCHAR(20), year INT, salary INT, PRIMARY KEY (name, post) ); INSERT INTO Employee VALUES ('Alexander', 'Confectioner', '+7 (943) 091-54-22', 1992, 451); INSERT INTO Employee VALUES ('Maria', 'Baker', '+7 (861) 864-32-87', 1995, 789); INSERT INTO Employee VALUES ('Dmitry', 'Decorator', '+7 (712) 306-19-45', 2001, 312); INSERT INTO Employee VALUES ('Sofia', 'Confectioner', '+7 (495) 521-78-93', 1993, 647); INSERT INTO Employee VALUES ('Ivan', 'Technologist', '+7 (268) 741-83-06', 2004, 902); INSERT INTO Employee VALUES ('Ekaterina', 'Packer', '+7 (985) 409-25-41', 1990, 405); INSERT INTO Employee VALUES ('Maksim', 'Baker', '+7 (167) 538-62-79', 1996, 714); INSERT INTO Employee VALUES ('Anna', 'The collector', '+7 (624) 072-14-58', 2003, 562); INSERT INTO Employee VALUES ('Artem', 'Confectioner', '+7 (989) 653-31-27', 2001, 833); INSERT INTO Employee VALUES ('Victoria', 'Commodity expert', '+7 (334) 427-90-15', 1994, 398); SELECT * FROM Employee; -- Таблица для Ingredient CREATE TABLE Ingredient ( kind VARCHAR(255), cost INT, quantity INT, PRIMARY KEY (kind) ); INSERT INTO Ingredient VALUES ('Flour', 8, 7); INSERT INTO Ingredient VALUES ('Sugar', 23, 21); INSERT INTO Ingredient VALUES ('Eggs', 17, 13); INSERT INTO Ingredient VALUES ('Oil', 5, 6); INSERT INTO Ingredient VALUES ('Vanilla', 12, 29); INSERT INTO Ingredient VALUES ('Chocolate', 29, 18); INSERT INTO Ingredient VALUES ('Cocoa powder', 3, 4); INSERT INTO Ingredient VALUES ('Nuts', 21, 22); INSERT INTO Ingredient VALUES ('Berries', 9, 11); INSERT INTO Ingredient VALUES ('Salt', 14, 15); SELECT * FROM Ingredient; -- Таблица для Order CREATE TABLE Orders ( ID INT, order_date VARCHAR(255), cost INT, PRIMARY KEY (ID) ); INSERT INTO Orders VALUES (764, '04-04-2024', 700); INSERT INTO Orders VALUES (284, '25-09-2024', 150); INSERT INTO Orders VALUES (298, '03-06-2024', 1100); INSERT INTO Orders VALUES (466, '04-11-2024', 500); INSERT INTO Orders VALUES (555, '23-02-2024', 1200); INSERT INTO Orders VALUES (896, '08-07-2024', 750); INSERT INTO Orders VALUES (214, '14-03-2024', 900); INSERT INTO Orders VALUES (154, '25-12-2024', 1000); INSERT INTO Orders VALUES (375, '02-08-2024', 650); INSERT INTO Orders VALUES (986, '07-05-2024', 1500); INSERT INTO Orders VALUES (777, '17-05-2024', 980); SELECT * FROM Orders; -- Таблица для Supplier CREATE TABLE Supplier ( manufacturer VARCHAR(255), phoneNumber VARCHAR(20), PRIMARY KEY (manufacturer) ); INSERT INTO Supplier VALUES ('Fresh Farms', '+7 (123) 456-78-90'); INSERT INTO Supplier VALUES ('Sweet Suppliers', '+7 (234) 567-89-01'); INSERT INTO Supplier VALUES ('Quality Ingredients', '+7 (345) 678-90-12'); INSERT INTO Supplier VALUES ('Gourmet Goods', '+7 (456) 789-01-23'); INSERT INTO Supplier VALUES ('Organic Delights', '+7 (567) 890-12-34'); INSERT INTO Supplier VALUES ('Daily Harvest', '+7 (678) 901-23-45'); INSERT INTO Supplier VALUES ('Baker/s Best', '+7 (789) 012-34-56'); INSERT INTO Supplier VALUES ('Local Farms', '+7 (890) 123-45-67'); INSERT INTO Supplier VALUES ('Exotic Flavors', '+7 (901) 234-56-78'); INSERT INTO Supplier VALUES ('Global Sweets', '+7 (012) 345-67-89'); SELECT * FROM Supplier; -- Таблица для Product CREATE TABLE Product ( appellation VARCHAR(255), purchasePrice INT, retailPrice INT, PRIMARY KEY (appellation) ); INSERT INTO Product VALUES ('Chocolate Cake', 150, 250); INSERT INTO Product VALUES ('Cupcake', 50, 100); INSERT INTO Product VALUES ('Brownie', 80, 150); INSERT INTO Product VALUES ('Cookie', 30, 60); INSERT INTO Product VALUES ('Strawberry', 120, 200); INSERT INTO Product VALUES ('Muffin', 40, 80); INSERT INTO Product VALUES ('Cheesecake', 100, 180); INSERT INTO Product VALUES ('Pudding', 30, 50); INSERT INTO Product VALUES ('Tarts', 70, 120); INSERT INTO Product VALUES ('Eclairs', 90, 160); SELECT * FROM Product; -- Связь между Confectionery и Customer (посетители) CREATE TABLE Visit ( title VARCHAR(255), city VARCHAR(255), name VARCHAR(255), phoneNumber VARCHAR(20), PRIMARY KEY (title, city, name, phoneNumber), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (name, phoneNumber) REFERENCES Customer (name, phoneNumber) ); INSERT INTO Visit VALUES ('Sweet Dreams', 'Moscow', 'Alexey', '+7 (123) 456-78-90'); INSERT INTO Visit VALUES ('Candy Land', 'Saint-Petersburg', 'Maria', '+7 (987) 654-32-10'); INSERT INTO Visit VALUES ('Chocolate World', 'Novosibirsk', 'Dmitry', '+7 (456) 789-01-23'); INSERT INTO Visit VALUES ('Cake Heaven', 'Yekaterinburg', 'Anna', '+7 (321) 098-76-54'); INSERT INTO Visit VALUES ('Pastry Palace', 'Kazan', 'Sergei', '+7 (678) 543-21-09'); INSERT INTO Visit VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Ekaterina', '+7 (234) 765-43-21'); INSERT INTO Visit VALUES ('Candy Castle', 'Samara', 'Ivan', '+7 (890) 123-45-67'); INSERT INTO Visit VALUES ('Sugar Rush', 'Omsk', 'Olga', '+7 (567) 890-12-34'); INSERT INTO Visit VALUES ('Delicacy Depot', 'Kaliningrad', 'Maksim', '+7 (345) 678-90-12'); INSERT INTO Visit VALUES ('Treat Factory', 'Volgograd', 'Elena', '+7 (901) 234-56-78'); INSERT INTO Visit VALUES ('Candy Land', 'Saint-Petersburg', 'Ivan', '+7 (890) 123-45-67'); INSERT INTO Visit VALUES ('Sweet Dreams', 'Moscow', 'Maria', '+7 (987) 654-32-10'); SELECT * FROM Visit; -- Связь между Confectionery и Employee (сотрудники) CREATE TABLE Cooperate ( title VARCHAR(255), city VARCHAR(255), name VARCHAR(255), post VARCHAR(255), PRIMARY KEY (title, city, name, post), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (name, post) REFERENCES Employee (name, post) ); INSERT INTO Cooperate VALUES ('Sweet Dreams', 'Moscow', 'Alexander', 'Confectioner'); INSERT INTO Cooperate VALUES ('Candy Land', 'Saint-Petersburg', 'Maria', 'Baker'); INSERT INTO Cooperate VALUES ('Chocolate World', 'Novosibirsk', 'Dmitry', 'Decorator'); INSERT INTO Cooperate VALUES ('Cake Heaven', 'Yekaterinburg', 'Sofia', 'Confectioner'); INSERT INTO Cooperate VALUES ('Pastry Palace', 'Kazan', 'Ivan', 'Technologist'); INSERT INTO Cooperate VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Ekaterina', 'Packer'); INSERT INTO Cooperate VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Ivan', 'Technologist'); INSERT INTO Cooperate VALUES ('Candy Castle', 'Samara', 'Maksim', 'Baker'); INSERT INTO Cooperate VALUES ('Sugar Rush', 'Omsk', 'Alexander', 'Confectioner'); INSERT INTO Cooperate VALUES ('Sugar Rush', 'Omsk', 'Anna', 'The collector'); INSERT INTO Cooperate VALUES ('Sugar Rush', 'Omsk', 'Artem', 'Confectioner'); INSERT INTO Cooperate VALUES ('Delicacy Depot', 'Kaliningrad', 'Artem', 'Confectioner'); INSERT INTO Cooperate VALUES ('Treat Factory', 'Volgograd', 'Victoria', 'Commodity expert'); SELECT * FROM Cooperate; -- Связь между Confectionery и Ingredient (используемые ингредиенты) CREATE TABLE Used ( title VARCHAR(255), city VARCHAR(255), kind VARCHAR(255), PRIMARY KEY (title, city, kind), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (kind) REFERENCES Ingredient (kind) ); INSERT INTO Used VALUES ('Sweet Dreams', 'Moscow', 'Flour'); INSERT INTO Used VALUES ('Candy Land', 'Saint-Petersburg', 'Sugar'); INSERT INTO Used VALUES ('Chocolate World', 'Novosibirsk', 'Eggs'); INSERT INTO Used VALUES ('Cake Heaven', 'Yekaterinburg', 'Oil'); INSERT INTO Used VALUES ('Pastry Palace', 'Kazan', 'Vanilla'); INSERT INTO Used VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Chocolate'); INSERT INTO Used VALUES ('Candy Castle', 'Samara', 'Cocoa powder'); INSERT INTO Used VALUES ('Sugar Rush', 'Omsk', 'Nuts'); INSERT INTO Used VALUES ('Delicacy Depot', 'Kaliningrad', 'Berries'); INSERT INTO Used VALUES ('Treat Factory', 'Volgograd', 'Salt'); INSERT INTO Used VALUES ('Delicacy Depot', 'Kaliningrad', 'Chocolate'); INSERT INTO Used VALUES ('Cake Heaven', 'Yekaterinburg', 'Sugar'); INSERT INTO Used VALUES ('Sweet Dreams', 'Moscow', 'Eggs'); SELECT * FROM Used; -- Связь между Confectionery и Order (заказы) CREATE TABLE Take ( title VARCHAR(255), city VARCHAR(255), ID INT, PRIMARY KEY (title, city, ID), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (ID) REFERENCES Orders (ID) ); INSERT INTO Take VALUES ('Sweet Dreams', 'Moscow', 764); INSERT INTO Take VALUES ('Candy Land', 'Saint-Petersburg', 284); INSERT INTO Take VALUES ('Chocolate World', 'Novosibirsk', 298); INSERT INTO Take VALUES ('Cake Heaven', 'Yekaterinburg', 466); INSERT INTO Take VALUES ('Pastry Palace', 'Kazan', 555); INSERT INTO Take VALUES ('Dessert Oasis', 'Nizhny Novgorod', 896); INSERT INTO Take VALUES ('Candy Castle', 'Samara', 214); INSERT INTO Take VALUES ('Sugar Rush', 'Omsk', 154); INSERT INTO Take VALUES ('Treat Factory', 'Volgograd', 986); INSERT INTO Take VALUES ('Treat Factory', 'Volgograd', 214); SELECT * FROM Take; -- Связь между Confectionery и Supplier (поставщики) CREATE TABLE Supply ( title VARCHAR(255), city VARCHAR(255), manufacturer VARCHAR(255), PRIMARY KEY (title, city, manufacturer), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (manufacturer) REFERENCES Supplier (manufacturer) ); INSERT INTO Supply VALUES ('Sweet Dreams', 'Moscow', 'Fresh Farms'); INSERT INTO Supply VALUES ('Candy Land', 'Saint-Petersburg', 'Sweet Suppliers'); INSERT INTO Supply VALUES ('Chocolate World', 'Novosibirsk', 'Quality Ingredients'); INSERT INTO Supply VALUES ('Cake Heaven', 'Yekaterinburg', 'Gourmet Goods'); INSERT INTO Supply VALUES ('Pastry Palace', 'Kazan', 'Organic Delights'); INSERT INTO Supply VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Daily Harvest'); INSERT INTO Supply VALUES ('Candy Castle', 'Samara', 'Baker/s Best'); INSERT INTO Supply VALUES ('Sugar Rush', 'Omsk', 'Local Farms'); INSERT INTO Supply VALUES ('Delicacy Depot', 'Kaliningrad', 'Exotic Flavors'); INSERT INTO Supply VALUES ('Treat Factory', 'Volgograd', 'Global Sweets'); INSERT INTO Supply VALUES ('Sugar Rush', 'Omsk', 'Daily Harvest'); INSERT INTO Supply VALUES ('Candy Land', 'Saint-Petersburg', 'Quality Ingredients'); SELECT * FROM Supply; -- Связь между Confectionery и Product (продукция) CREATE TABLE Fabricate ( title VARCHAR(255), city VARCHAR(255), appellation VARCHAR(255), PRIMARY KEY (title, city, appellation), FOREIGN KEY (title, city) REFERENCES Confectionery (title, city), FOREIGN KEY (appellation) REFERENCES Product (appellation) ); INSERT INTO Fabricate VALUES ('Sweet Dreams', 'Moscow', 'Chocolate Cake'); INSERT INTO Fabricate VALUES ('Candy Land', 'Saint-Petersburg', 'Cupcake'); INSERT INTO Fabricate VALUES ('Chocolate World', 'Novosibirsk', 'Brownie'); INSERT INTO Fabricate VALUES ('Cake Heaven', 'Yekaterinburg', 'Cookie'); INSERT INTO Fabricate VALUES ('Pastry Palace', 'Kazan', 'Strawberry'); INSERT INTO Fabricate VALUES ('Dessert Oasis', 'Nizhny Novgorod', 'Muffin'); INSERT INTO Fabricate VALUES ('Candy Castle', 'Samara', 'Cheesecake'); INSERT INTO Fabricate VALUES ('Sugar Rush', 'Omsk', 'Pudding'); INSERT INTO Fabricate VALUES ('Delicacy Depot', 'Kaliningrad', 'Tarts'); INSERT INTO Fabricate VALUES ('Treat Factory', 'Volgograd', 'Eclairs'); SELECT * FROM Fabricate; -- Связь между Customer и Order (заказы клиентов) CREATE TABLE Make ( ID INT, name VARCHAR(255), phoneNumber VARCHAR(20), PRIMARY KEY (name, phoneNumber, ID), FOREIGN KEY (name, phoneNumber) REFERENCES Customer (name, phoneNumber), FOREIGN KEY (ID) REFERENCES Orders (ID) ); INSERT INTO Make VALUES (764, 'Alexey', '+7 (123) 456-78-90'); INSERT INTO Make VALUES (284, 'Maria', '+7 (987) 654-32-10'); INSERT INTO Make VALUES (298, 'Dmitry', '+7 (456) 789-01-23'); INSERT INTO Make VALUES (466, 'Anna', '+7 (321) 098-76-54'); INSERT INTO Make VALUES (555, 'Sergei', '+7 (678) 543-21-09'); INSERT INTO Make VALUES (896, 'Ekaterina', '+7 (234) 765-43-21'); INSERT INTO Make VALUES (214, 'Ivan', '+7 (890) 123-45-67'); INSERT INTO Make VALUES (154, 'Olga', '+7 (567) 890-12-34'); INSERT INTO Make VALUES (375, 'Maksim', '+7 (345) 678-90-12'); INSERT INTO Make VALUES (986, 'Elena', '+7 (901) 234-56-78'); INSERT INTO Make VALUES (777, 'Olga', '+7 (567) 890-12-34'); SELECT * FROM Make; -- Связь между Ingredient и Supplier (поставщики ингредиентов) CREATE TABLE Deliver ( manufacturer VARCHAR(255), kind VARCHAR(255), PRIMARY KEY (kind, manufacturer), FOREIGN KEY (kind) REFERENCES Ingredient (kind), FOREIGN KEY (manufacturer) REFERENCES Supplier (manufacturer) ); INSERT INTO Deliver VALUES ('Fresh Farms', 'Flour'); INSERT INTO Deliver VALUES ('Fresh Farms', 'Sugar'); INSERT INTO Deliver VALUES ('Sweet Suppliers', 'Sugar'); INSERT INTO Deliver VALUES ('Quality Ingredients', 'Eggs'); INSERT INTO Deliver VALUES ('Gourmet Goods', 'Oil'); INSERT INTO Deliver VALUES ('Gourmet Goods', 'Cocoa powder'); INSERT INTO Deliver VALUES ('Organic Delights', 'Vanilla'); INSERT INTO Deliver VALUES ('Daily Harvest', 'Chocolate'); INSERT INTO Deliver VALUES ('Baker/s Best', 'Cocoa powder'); INSERT INTO Deliver VALUES ('Fresh Farms', 'Vanilla'); INSERT INTO Deliver VALUES ('Local Farms', 'Nuts'); INSERT INTO Deliver VALUES ('Exotic Flavors', 'Berries'); INSERT INTO Deliver VALUES ('Exotic Flavors', 'Vanilla'); INSERT INTO Deliver VALUES ('Global Sweets', 'Salt'); SELECT * FROM Deliver; -- Связь между Product и Ingredient (ингредиенты в продукте) CREATE TABLE Employ ( appellation VARCHAR(255), kind VARCHAR(255), PRIMARY KEY (appellation, kind), FOREIGN KEY (appellation) REFERENCES Product (appellation), FOREIGN KEY (kind) REFERENCES Ingredient (kind) ); INSERT INTO Employ VALUES ('Chocolate Cake', 'Flour'); INSERT INTO Employ VALUES ('Chocolate Cake', 'Eggs'); INSERT INTO Employ VALUES ('Chocolate Cake', 'Sugar'); INSERT INTO Employ VALUES ('Chocolate Cake', 'Cocoa powder'); INSERT INTO Employ VALUES ('Cupcake', 'Sugar'); INSERT INTO Employ VALUES ('Brownie', 'Eggs'); INSERT INTO Employ VALUES ('Brownie', 'Cocoa powder'); INSERT INTO Employ VALUES ('Cookie', 'Oil'); INSERT INTO Employ VALUES ('Strawberry', 'Vanilla'); INSERT INTO Employ VALUES ('Muffin', 'Chocolate'); INSERT INTO Employ VALUES ('Cheesecake', 'Cocoa powder'); INSERT INTO Employ VALUES ('Pudding', 'Nuts'); INSERT INTO Employ VALUES ('Pudding', 'Vanilla'); INSERT INTO Employ VALUES ('Pudding', 'Chocolate'); INSERT INTO Employ VALUES ('Tarts', 'Berries'); INSERT INTO Employ VALUES ('Eclairs', 'Salt'); INSERT INTO Employ VALUES ('Cookie', 'Nuts'); SELECT * FROM Employ; -- Связь между Order и Product (включение продукции в заказ) CREATE TABLE Include ( ID INT, appellation VARCHAR(255), PRIMARY KEY (ID, appellation), FOREIGN KEY (ID) REFERENCES Orders (ID), FOREIGN KEY (appellation) REFERENCES Product (appellation) ); INSERT INTO Include VALUES (764, 'Chocolate Cake'); INSERT INTO Include VALUES (764, 'Cookie'); INSERT INTO Include VALUES (284, 'Cupcake'); INSERT INTO Include VALUES (298, 'Brownie'); INSERT INTO Include VALUES (466, 'Cookie'); INSERT INTO Include VALUES (764, 'Brownie'); INSERT INTO Include VALUES (555, 'Strawberry'); INSERT INTO Include VALUES (555, 'Brownie'); INSERT INTO Include VALUES (555, 'Cheesecake'); INSERT INTO Include VALUES (896, 'Muffin'); INSERT INTO Include VALUES (284, 'Strawberry'); INSERT INTO Include VALUES (214, 'Cheesecake'); INSERT INTO Include VALUES (154, 'Pudding'); INSERT INTO Include VALUES (375, 'Tarts'); INSERT INTO Include VALUES (986, 'Eclairs'); INSERT INTO Include VALUES (154, 'Tarts'); SELECT * FROM Include; DELETE FROM Cooperate WHERE title = 'Treat Factory' AND city = 'Volgograd'; DELETE FROM Fabricate WHERE title = 'Treat Factory' AND city = 'Volgograd'; DELETE FROM Supply WHERE title = 'Treat Factory' AND city = 'Volgograd'; DELETE FROM Take WHERE title = 'Treat Factory' AND city = 'Volgograd'; DELETE FROM Used WHERE title = 'Treat Factory' AND city = 'Volgograd'; DELETE FROM Visit WHERE title = 'Treat Factory' AND city = 'Volgograd'; -- 1. Триггер уровня строки, использующий псевдозаписи `OLD` и `NEW`, -- предназначенный для поддержания ссылочной целостности ---- Этот триггер срабатывает перед удалением записи из таблицы Confectionery. ---- Если существует хотя бы один активный заказ (связанный с кондитерской через ---- таблицу `Take`), попытка удалить кондитерскую будет заблокирована. CREATE TABLE AuditLog ( id INT AUTO_INCREMENT PRIMARY KEY, action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, table_name VARCHAR(255), action_type VARCHAR(50), old_title VARCHAR(255), old_city VARCHAR(255), new_title VARCHAR(255), new_city VARCHAR(255), message TEXT ); DELIMITER // CREATE TRIGGER prevent_delete_confectionery BEFORE DELETE ON Confectionery FOR EACH ROW BEGIN -- Проверяем, есть ли заказы, связанные с этой кондитерской IF EXISTS (SELECT 1 FROM Take WHERE title = OLD.title AND city = OLD.city) THEN -- Записываем в таблицу аудита INSERT INTO AuditLog (table_name, action_type, old_title, old_city, new_title, new_city) VALUES ('Confectionery', 'DELETE', OLD.title, OLD.city, NULL, NULL); END IF; -- Проверяем, есть ли поставки, связанные с этой кондитерской IF EXISTS (SELECT 1 FROM Supply WHERE title = OLD.title AND city = OLD.city) THEN -- Записываем в таблицу аудита INSERT INTO AuditLog (table_name, action_type, old_title, old_city, new_title, new_city) VALUES ('Confectionery', 'DELETE', OLD.title, OLD.city, NULL, NULL); END IF; -- Проверяем, есть ли продукты, связанные с этой кондитерской IF EXISTS (SELECT 1 FROM Fabricate WHERE title = OLD.title AND city = OLD.city) THEN -- Записываем в таблицу аудита INSERT INTO AuditLog (table_name, action_type, old_title, old_city, new_title, new_city, ) VALUES ('Confectionery', 'DELETE', OLD.title, OLD.city, NULL, NULL); END IF; END; // DELIMITER ; CREATE OR REPLACE TRIGGER audit_delete_confectionery AFTER DELETE ON Confectionery FOR EACH ROW BEGIN INSERT INTO AuditLog (title, city, deleted_at) VALUES (OLD.title, OLD.city, NOW()); END audit_delete_confectionery; CREATE TRIGGER audit_confectionery_update AFTER UPDATE ON Confectionery FOR EACH ROW BEGIN DECLARE v_message TEXT; -- Если изменения касаются поля title или city IF OLD.title != NEW.title OR OLD.city != NEW.city THEN INSERT INTO AuditLog (table_name, action_type, old_title, old_city, new_title, new_city, message) VALUES ('Confectionery', 'UPDATE', OLD.title, OLD.city, NEW.title, NEW.city, v_message); END IF; END; // DELIMITER ; DELETE FROM Confectionery WHERE title = 'Treat Factory' AND city = 'Volgograd'; SELECT * FROM AuditLog;