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) ON DELETE CASCADE, FOREIGN KEY (name, phoneNumber) REFERENCES Customer (name, phoneNumber) ON DELETE CASCADE ); 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) ON DELETE CASCADE, FOREIGN KEY (name, post) REFERENCES Employee (name, post) ON DELETE CASCADE ); 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) ON DELETE CASCADE, FOREIGN KEY (kind) REFERENCES Ingredient (kind) ON DELETE CASCADE ); 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) ON DELETE CASCADE, FOREIGN KEY (ID) REFERENCES Orders (ID) ON DELETE CASCADE ); 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) ON DELETE CASCADE, FOREIGN KEY (manufacturer) REFERENCES Supplier (manufacturer) ON DELETE CASCADE ); 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) ON DELETE CASCADE, FOREIGN KEY (appellation) REFERENCES Product (appellation) ON DELETE CASCADE ); 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) ON DELETE CASCADE, FOREIGN KEY (ID) REFERENCES Orders (ID) ON DELETE CASCADE ); 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) ON DELETE CASCADE, FOREIGN KEY (manufacturer) REFERENCES Supplier (manufacturer) ON DELETE CASCADE ); 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) ON DELETE CASCADE, FOREIGN KEY (kind) REFERENCES Ingredient (kind) ON DELETE CASCADE ); 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) ON DELETE CASCADE, FOREIGN KEY (appellation) REFERENCES Product (appellation) ON DELETE CASCADE ); 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; -- 3. Триггер с условием `WHEN` для изменения стоимости заказа в зависимости от условий ---- Этот триггер срабатывает перед вставкой нового заказа в таблицу Orders. ---- Если стоимость заказа превышает 1000, применяется скидка 10% (умножение на 0.9). ---- Это помогает автоматически учитывать скидки для крупных заказов. CREATE TABLE Orders_Audit ( audit_id SERIAL PRIMARY KEY, order_id INT, old_cost DECIMAL(10, 2), new_cost DECIMAL(10, 2), action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, change_type VARCHAR(50) ); CREATE OR REPLACE FUNCTION apply_discount() RETURNS TRIGGER AS $$ BEGIN -- Запись в таблицу аудита перед изменением INSERT INTO Orders_Audit (order_id, old_cost, new_cost, change_type) VALUES (NEW.id, NEW.cost, NEW.cost * 0.9, 'Discount Applied'); -- Применяем скидку 10% NEW.cost := NEW.cost * 0.9; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER apply_discount_for_large_orders BEFORE INSERT ON Orders FOR EACH ROW WHEN (NEW.cost > 1000) EXECUTE FUNCTION apply_discount(); INSERT INTO Orders (id, order_date, cost) VALUES (888, '2024-06-17', 1500); INSERT INTO Orders (id, order_date, cost) VALUES (111, '2024-06-18', 800); -- Проверка данных в таблице Orders SELECT * FROM Orders; -- Проверка данных в таблице аудита SELECT * FROM Orders_Audit;