i have 2 tables: category, location.
i create the tables:
create database tourist;
grant all on tourist.* to dbuser;
use tourist;
create table category(id integer NOT NULL AUTO_INCREMENT,season varchar(20),num_summer integer,num_winter integer,PRIMARY KEY(id));
create table location(code integer NOT NULL AUTO_INCREMENT,town varchar(20),hotel varchar(20),price integer,star integer,media varchar(70),PRIMARY KEY(code));
i want now to create a trigger on category that when is inserted in field <season> a season , if it is inserted before to count how many for that season.
Inserted winter and then again winter the num_winter should be 2.
i created this but doesn't work:
create trigger count_hotels
after insert on category
for each row
begin
if season='Summer' then
update category
set categoty.num_summer = category.num_summer + 1
else if season='Winter' then
update category
set categoty.num_winter = category.num_winter + 1
end if;
end;
If anyone could help i would appreciate it.