#1 - количество поставленных товаров на организацию
create or replace algorithm = temptable view _tmp as select организация.name, sum(поставки.count) as cnt from организация, поставки
where поставки.idорганизации = организация.idорганизации
group by поставки.idорганизации;

select max(cnt) from _tmp;
select * from _tmp;

#1 - вывод поставок на организацию
create or replace algorithm = undefined view tab as select организация.name, count(поставки.count) as cnt from организация, поставки
where поставки.idорганизации = организация.idорганизации
group by поставки.idорганизации;

select * from tab;

#2 - обновляемое представление, не позволяющее выполнить insert
create or replace algorithm = merge view w (idтоварa, name, bonus) as select  idтоварa, name, (count+3) from товар;

select * from w;
insert into w (idтоварa, name, bonus) values(77, 'jjj', 777);

#3 - обновляемое представление, позволяющее выполнить insert
create or replace algorithm = merge view w (id, _name, _count) as select  idтоварa, name, count from товар
where name like 'IPhone XR%';

select * from w;

#4 - Создать вложенное обновляемое представление с проверкой ограничений (WITH CHECK OPTION)
create or replace view _w as select id, _name, _count from w
where _name like 'IPhone X%'
with cascaded check option; # if local, то заносится в таблицу

insert into _w (id, _name, _count) values(13,'IPhone XS', 1);

select max(_count) from _w;
select * from _w;


#5
delimiter //
create trigger trg
after delete on организация
for each row
begin
	delete from поставки where поставки.idорганизации = old.idорганизации;
end;
//

delimiter ;

#6
delimiter //
create trigger _t before update on склад
for each row
begin
	if new.num_ячейки < 0 then signal sqlstate '45000' set message_text = 'error';
    elseif new.num_ячейки >= 0 then set new.num_ячейки = new.num_ячейки;
    end if;
end;
//

delimiter ;

update склад set num_ячейки = -9 where idтовара = 12;
update склад set num_ячейки = 55 where idтовара = 12;

#7
delimiter //
create trigger trig
before insert on склад
for each row
begin
    update товар set count = (count + 1) where idтоварa = товар.idтоварa;
end;
//

delimiter ;


#9.1
delimiter //
create trigger _instime
after insert on товар for each row
begin
	insert into new_table
    (
    idтовара,
    data,
    имя_товара,
    count)
    values
    (
    new.idтоварa,
    sysdate(),
    new.name,
    new.count);
    end;
//
delimiter ;

#8.1
delimiter //
create trigger t1
before insert on товар for each row
begin
	if new.count < 0 then signal sqlstate '45000' set message_text = 'error';
    elseif new.count >= 0 then set new.idтоварa = new.idтоварa, new.name = new.name, new.count = new.count;
    end if;
end;
//
delimiter ;

delimiter //
create trigger t1
before insert on товар for each row
begin
	if new.count < 0 then signal sqlstate '45000' set message_text = 'error';
    elseif new.count >= 0 then set new.idтоварa = new.idтоварa, new.name = new.name, new.count = new.count;
    end if;
end;
//
delimiter ;

insert into товар values(99, 'iPhone 3' , 1);
drop trigger _instime;
drop trigger t1;


select * from new_table;

insert into товар values(15, 'iPhone 4', 11);

SET FOREIGN_KEY_CHECKS=0;
insert into склад values(1, 62);
SET FOREIGN_KEY_CHECKS=1;

drop trigger _t;
drop trigger trig;
drop trigger _ins;

SET FOREIGN_KEY_CHECKS=0;
delete from организация where idорганизации = 2;
select * from  поставки;
SET FOREIGN_KEY_CHECKS=1;