SQL ukazi za funkcije, procedure in triggerje

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 ;