---

create table products(id serial primary key, productname varchar(50), manufacturer varchar(40), productcount int, price decimal(10, 2));
create table productbatch (id serial primary key, qunatity int, productid int references products(id));
create table history (id serial primary key, productid int references products(id), opreration varchar(400), createat date);
create table save_log (id serial primary key, d_time date, mess varchar(50), table_n varchar(50));
create or replace function products_log_trigger() returns trigger as $$ begin insert into save_log (d_time, mess, table_n) values (current_timestamp, tg_op, tg_table_name); return null; end; $$ language plpgsql;
create trigger products_changes_log after insert or update or delete on products for each statement execute function products_log_trigger();
insert into products values (1, 'iphone 13', 'Apple', 10, 50000.00);
create or replace function batch_log_trigger() returns trigger as $$ begin if tg_op = 'insert' then insert into save_log (d_time, mess, table_n) values (current_timestamp, 'insert', tg_table_name); elsif tg_op = 'update' then insert into save_log (d_time, mess, table_n) values (current_timestamp, 'update', tg_table_name); elsif tg_op = 'delete' then insert into save_log (d_time, mess, table_n) values (current_timestamp, 'delete', tg_table_name); end if; if tg_op = 'delete' then return old; else return new; end if; end;$$ language plpgsql;
create trigger batch_changes_log after insert or update or delete on productbatch for each row execute function batch_log_trigger();
insert into productbatch values (1, 20, 1);
create or replace function set_product_count() returns trigger as $$ begin new.productcount := 0; return new; end; $$ language plpgsql;
create trigger set_product_count before insert on products for each row execute function set_product_count();
insert into products (id, productname, manufacturer, productcount, price) values (2, 'test phone', 'Test Brand', 99, 1000.00);
select productname, productcount from products where productname = 'test phone';
create or replace function update_products_count() returns trigger as $$ begin update products set productcount = productcount + new.qunatity where id = new.productid; return new; end; $$ language plpgsql;
create trigger update_product_count after insert on productbatch for each row execute function update_products_count();
insert into productbatch (qunatity, productid) values (15, 1);
select productname, productcount from products where id = 1;
create or replace function check_price() returns trigger as $$ begin if new.price < 10 then raise exception 'Цена продукта ниже 10. Пожалуйста, уточните цену.'; end if; return new; end; $$ language plpgsql;
create trigger check_price before update on products for each row execute function check_price();
update products set price = 4.40 where id = 1;
create or replace function log_deleted_product() returns trigger as $$ begin insert into history (productid, opreration, createat) values (old.id, 'Удаен товар' || old.productname || 'фирма' || old.manufacturer, now()); return old; end; $$ language plpgsql;
create trigger products_delete after delete on products for each row execute function log_deleted_product();
delete from products where id = 3;
create or replace function log_product_update() returns trigger as $$ begin if old.productname = new.productname then insert into history (productid, opreration, createat) values (old.id, 'обнавлен товар' || old.productname || ',фирма' || old.manufacturer, now()); else insert into history (productid, opreration, createat) values (old.id, 'Обнавлен товар' || old.productname || 'на' || new.productname || ',фирма' || old.manufacturer, now()); end if; return new; end; $$ language plpgsql;
create trigger products_update after update on products for each row execute function log_product_update();
update products set productname = 'iphone 13 pro' where id = 1;