-- triggers

-- create trigger trigger_name
-- trigger type
-- on table_name for each row
-- begin
-- 		statement_one
--     	statement_two
-- end

-- drop trigger if exists trigger_name;

-- https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

drop table if exists sailorratingupdates;

create table sailorratingupdates(
	sid integer primary key,
    sname varchar(30),
    oldrating integer,
    newrating integer,
    updatedat timestamp
);

drop trigger if exists onupdatesailorratings;

create trigger onupdatesailorratings
after update
on sailors for each row
insert into sailorratingupdates(sid, sname, oldrating, newrating, updatedat)
values(new.sid, new.sname, old.rating, new.rating, now());

update sailors
set rating = 8
where sid = 22;


create table sailorratingupdatereports(
	upto timestamp,
    totalupdates integer
);

-- events
drop event if exists updatesailorratingupdatereports;

delimiter //
create event updatesailorratingupdatereports
on schedule at current_timestamp + interval 1 minute
-- on every interval
-- starts timestamp [+interval] ends timestamp [+interval]
do 
begin
	declare total integer;
    select count(*) into total from sailorratingupdates;
	insert into sailorratingupdatereports(upto, totalupdates)
    values(current_timestamp(), total);
end //
delimiter ;