97 lines
4.6 KiB
SQL
97 lines
4.6 KiB
SQL
-- Триггеры для базы данных интернет-магазина
|
|
-- PostgreSQL
|
|
--
|
|
-- ВАЖНО: Этот файл должен выполняться ПОСЛЕ schema.sql
|
|
-- Сначала создайте структуру базы данных (schema.sql), затем создайте триггеры (triggers.sql)
|
|
-- Функции должны быть созданы в schema.sql перед выполнением этого файла
|
|
|
|
-- Триггеры для автоматического обновления updated_at
|
|
DROP TRIGGER IF EXISTS update_resources_updated_at ON resources;
|
|
CREATE TRIGGER update_resources_updated_at BEFORE UPDATE ON resources
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_actions_updated_at ON actions;
|
|
CREATE TRIGGER update_actions_updated_at BEFORE UPDATE ON actions
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_permissions_updated_at ON permissions;
|
|
CREATE TRIGGER update_permissions_updated_at BEFORE UPDATE ON permissions
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_roles_updated_at ON roles;
|
|
CREATE TRIGGER update_roles_updated_at BEFORE UPDATE ON roles
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
|
|
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_categories_updated_at ON categories;
|
|
CREATE TRIGGER update_categories_updated_at BEFORE UPDATE ON categories
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_order_statuses_updated_at ON order_statuses;
|
|
CREATE TRIGGER update_order_statuses_updated_at BEFORE UPDATE ON order_statuses
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_payment_methods_updated_at ON payment_methods;
|
|
CREATE TRIGGER update_payment_methods_updated_at BEFORE UPDATE ON payment_methods
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_payment_statuses_updated_at ON payment_statuses;
|
|
CREATE TRIGGER update_payment_statuses_updated_at BEFORE UPDATE ON payment_statuses
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_products_updated_at ON products;
|
|
CREATE TRIGGER update_products_updated_at BEFORE UPDATE ON products
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_addresses_updated_at ON addresses;
|
|
CREATE TRIGGER update_addresses_updated_at BEFORE UPDATE ON addresses
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_orders_updated_at ON orders;
|
|
CREATE TRIGGER update_orders_updated_at BEFORE UPDATE ON orders
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_cart_items_updated_at ON cart_items;
|
|
CREATE TRIGGER update_cart_items_updated_at BEFORE UPDATE ON cart_items
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_reviews_updated_at ON reviews;
|
|
CREATE TRIGGER update_reviews_updated_at BEFORE UPDATE ON reviews
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_payments_updated_at ON payments;
|
|
CREATE TRIGGER update_payments_updated_at BEFORE UPDATE ON payments
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Триггеры для обновления рейтинга товара
|
|
DROP TRIGGER IF EXISTS update_product_rating_on_review_insert ON reviews;
|
|
CREATE TRIGGER update_product_rating_on_review_insert
|
|
AFTER INSERT ON reviews
|
|
FOR EACH ROW
|
|
WHEN (NEW.is_approved = TRUE)
|
|
EXECUTE FUNCTION update_product_rating();
|
|
|
|
DROP TRIGGER IF EXISTS update_product_rating_on_review_update ON reviews;
|
|
CREATE TRIGGER update_product_rating_on_review_update
|
|
AFTER UPDATE ON reviews
|
|
FOR EACH ROW
|
|
WHEN (NEW.is_approved != OLD.is_approved OR NEW.rating != OLD.rating)
|
|
EXECUTE FUNCTION update_product_rating();
|
|
|
|
DROP TRIGGER IF EXISTS update_product_rating_on_review_delete ON reviews;
|
|
CREATE TRIGGER update_product_rating_on_review_delete
|
|
AFTER DELETE ON reviews
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_product_rating();
|
|
|
|
-- Триггер для автоматической генерации номера заказа
|
|
DROP TRIGGER IF EXISTS generate_order_number_trigger ON orders;
|
|
CREATE TRIGGER generate_order_number_trigger
|
|
BEFORE INSERT ON orders
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION generate_order_number();
|
|
|