lr4

#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товара, 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товара, name, count from товар
where name like 'IPhone X%';

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 ;

drop trigger trg;

insert into организация values(6, 'ggg', 'Rostov');
insert into поставки values(6, 3, '2019-03-09', 9);

SET FOREIGN_KEY_CHECKS=0;
delete from организация where id = 6;
SET FOREIGN_KEY_CHECKS=1;

#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 ;

drop trigger _t;

SET FOREIGN_KEY_CHECKS=0;
update склад set numячейки = 55 where idтовара = 2;
SET FOREIGN_KEY_CHECKS=1;

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

delimiter ;

insert into товар values(10, 'iPhone 7', 1);
insert into склад values(10,68);
 update товар set count = 2 where idтовара = 10;

drop trigger trig;

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

#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товара = new.idтовара, new.name = new.name, new.count = new.count;
    end if;
end;
//
delimiter ;

drop trigger t1;

SET FOREIGN_KEY_CHECKS=0;
insert into товар values(6, 'iPad mini', 3);
SET FOREIGN_KEY_CHECKS=1;

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

drop trigger t2;

SET FOREIGN_KEY_CHECKS=0;
insert into товар values(6, 'iPad mini', 3);
SET FOREIGN_KEY_CHECKS=1;

#9.1
delimiter //
create trigger _instime
after insert on товар for each row
follows t1
begin
	insert into new_table
    (
    операция,
    data,
    idтовара_до,
    имя_товара_до,
    count_до,
    idтовара_после,
    имя_товара_после,
    count_после
    )
    values
    (
    'Вставка',
    sysdate(),
    null,
    null,
    null,
    new.idтовара,
    new.name,
    new.count);
    end;
//
delimiter ;

drop trigger _instime;

SET FOREIGN_KEY_CHECKS=0;
insert into товар values(7, 'iPhone XR', 1);
insert into товар values(9, 'iPhone 8', 1);
insert into склад values(9,27);
SET FOREIGN_KEY_CHECKS=1;

#9.2
delimiter //
create trigger _upd
after update on товар for each row
begin
	insert into new_table
    (
    операция,
    data,
    idтовара_до,
    имя_товара_до,
    count_до,
    idтовара_после,
    имя_товара_после,
    count_после
    )
    values
    (
    'Обновление',
    sysdate(),
    old.idтовара,
    old.name,
    old.count,
    new.idтовара,
    new.name,
    new.count);
    end;
//
delimiter ;

drop trigger _upd;

update товар set name = 'iPhone 6s' where idтовара = 3;

#9.3
delimiter //
create trigger _del
after delete on товар for each row
begin
	insert into new_table
    (
    операция,
    data,
    idтовара_до,
    имя_товара_до,
    count_до,
    idтовара_после,
    имя_товара_после,
    count_после
    )
    values
    (
    'Удаление',
    sysdate(),
    old.idтовара,
    old.name,
    old.count,
    null,
    null,
    null);
    end;
//
delimiter ;

drop trigger _del;

SET FOREIGN_KEY_CHECKS=0;
delete from товар where idтовара = 7;
SET FOREIGN_KEY_CHECKS=1;

delete from товар where idтовара = 9;