2021-08-28

delimiter $$
create trigger payment_after_insert
	after insert  on payments
    for each row

begin
	update invoices
    set payment_total = payment_total + new.amount
	where invoice_id = new.invoice_id;
end $$

delimiter ;

create trigger payment_after_delete
	after delete  on payments
    for each row

begin
	update invoices
    set payment_total = payment_total - old.amount
	where invoice_id = old.invoice_id;
end $$

delimiter ;

drop trigger if exists payments_after_insert;
drop trigger if exists payments_after_delete;

delimiter $$
create trigger payments_after_insert
	after insert on payments
    for each row
begin
	update invoices
    set payment_total = payment_total + new.amount
    where invoice_id = new.invoice_id;
    
    insert into payments_audit
    values(new.client_id, new.date, new.amount,'Insert', now());
end $$

delimiter $$
create trigger payments_after_delete
	after delete on payments
    for each row
begin
	update invoices
    set payment_total = payment_total - old.amount
    where invoice_id = old.invoice_id;
    
    insert into payments_audit
    values(old.client_id, old.date, old.amount,'Delete', now());
end $$

delimiter ;

show variables like 'event%';

delimiter $$

create event yearly_delete_stale_audit_rows
on schedule
	every 1 year starts '2019-01-01' ends '2029-01-01'
do begin
	delete from payments_audit
    where action_date < now() - interval 1 year;
    
end $$

delimiter ;

use sql_store;

start transaction;
insert into orders(customer_id, order_date, status)
values(1, '2019-01-01', 1);

insert into order_items
values(last_insert_id(),1,1,1);

commit;

show variables like 'autocommit';