---

create table employees(employee_id serial primary key, first_name varchar(50), last_name varchar(50), job_id int, salary decimal(10, 2));
insert into employees(first_name, last_name, job_id, salary) values ('Иван', 'Иванов', 1, 5000.00), ('Петр', 'Петров', 2, 3400.00), ('Анна', 'Сидорова', null, null);
create table audit_emp_values(currentuser varchar(50), currenttimestamp timestamp, employee_id int, first_name varchar(50), last_name varchar(50), old_job_id int, new_job_id int, old_salary decimal(10,), new_salary decimal(10, 2));
create or replace function process_audit_emp_values() returns trigger language plpgsql as $$ begin if (old.job_id is distinct from new.job_id) or (old.salary is distinct from new.salary) then insert into audit_emp_values(currentuser, currenttimestamp, employee_id, first_name, last_name, old_job_id, new_job_id, old_salary, new_salary) values (current_user, current_timestamp, old.employee_id, old.first_name, old.last_name, old.job_id, new.job_id, old.salary, new.salary) end if; return then; end $$;
create trigger trg_audit_emp_values after update of job_id, salary from employees for each row execute function process_audit_emp_values();
=======
create atble audit_emp_idu(currentuser varchar(50), currenttimestamp timestamp, employee_id int, first_name varchar(50), last_name varchar(50), operation varchar(50));
create or replace function process_audit_emp_idu() returns trigger language plpgsql as $$ begin if (tg_op = 'DELETE') then emp_record := old; else emp_record := new; end if; insert into audit_emp_idu(currentuser, currenttimestamp, employee_id, first_name, last_name, operation) values (current_user, current_timestamp, emp_record.employee_id, emp_record.first_name, emp_record.last_name, tg_op); if (tg_op = 'DELETE') then return old; else return new; end if; end $$;
create trigger trg_audit_emp_idu after insert or update or delete on employees for each row execute function process_audit_emp_idu();
========
create table products (product_id serial primary key, product_name varchar(50), price decimal(10, 2));
create table products_audit (currentuser varchar(50), currenttimestamp timestamp, operation varchar(50), product_id int, product_details jsonb);
create or replace function process_products_audit() returns trigger language plpgsql as $$ begin if (tg_op = 'INSERT' then insert into products_audit (currentuser, currenttimestamp, operation, product_id, product_details) values (current_user, current_timestamp, 'INSERT', new.product_id, to_jsonb(new)); else (tg_op = 'DELETE' then insert into products_audit (currentuser, currenttimestamp, operation, product_id, product_details) values (current_user, current_timestamp, 'DELETE', new.product_id, to_jsonb(new)); END IF; if (tg_op = 'DELETE') then return old; else return new; end if; end $$;
create trigger trg_products_audit after insert or delete on products for each row execute function process_products_audit();
======
create table order_items (order_id int, product_id int, quantity int, primary key (order_id, product_id));
create or replace function process_order_items_insert() returns trigger language plpgsql as $$ begin if (select 1 from order_items where order_id = new.order_id and product_id = new.product_id) then update order_items set quantity = quantity + new.quantity where order_id = new.order_id and product_id = new.product_id; return null; end if; return new; end $$;
create trigger trg_order_items_insert before insert on order_items for each row execute function process_order_items_insert();