use sandbox;
drop table if exists jadr321;
create table jadr321 as select * from tup.jadralec;
alter table jadr321 add StRez integer default 0 not null;
drop table if exists rez321;
create table Rez321 as select * from tup.rezervacija;
update jadr321 j set StRez = (select count(*) from rez321 r where r.jid=j.jid);
drop function if exists CountStRez;
delimiter //
create function CountStRez(jid integer)
returns integer
begin
declare x integer;
select count(*) into x from rez321 r where r.jid=jid;
return x;
end//
delimiter ;
select countstrez(22);
drop trigger if exists UpdStRez;
delimiter //
create trigger UpdStRez after insert on Rez321
for each row
begin
update jadr321 set strez = CountStRez(new.jid) where jadr321.jid=new.jid;
end//
delimiter ;
select * from jadr321;
select * from rez321;
insert into rez321 values(22,101,date'2020.11.17');
drop procedure if exists StejBarve;
delimiter //
create procedure StejBarve(in barvaa varchar(10), out st integer) 
BEGIN   
   SELECT COUNT(*) INTO st
   from tup.coln
   where barva=barvaa;
END//
delimiter ;
call StejBarve('rdeca',@x);
select @x;
show triggers --pokaze vse bazne prozilce
drop trigger if exists test;
delimiter //
CREATE TRIGGER test 
after UPDATE on jadr321
for EACH ROW
BEGIN
	if new.rating > 5 THEN
    	UPDATE jadr321 set rating = new.rating - 1;
	END IF;
END//
delimiter ;
Последна промена: среда, 17 ноември 2021, 14:29