MySQL Trigger

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.

Hello,

OK I see a couple of things but want to make sure I have the right idea. If I am reading the code correctly you are running this trigger from the category table to update the record in category that you just entered....

One problem is you can't add to a field with a value of NULL so you need to change your default for the fields to be:

create table category(id integer NOT NULL AUTO_INCREMENT,season varchar(20),
num_summer integer default '0',
num_winter integer default '0' ,
PRIMARY KEY(id));

Based on that the row would always be 1 in either category.num_summer or category.num_winter in every record you add. In the Category table you would have the rows:

1, summer, 1, 0
2, summer, 1, 0
3, winter, 0, 1
4, summer, 1, 0
5, summer, 1, 0
6, winter, 0, 1
7, winter, 0, 1
8, winter, 0, 1
etc.

Did you intend to have the category field in the location table and then do a post to the category table total field after they insert the record in location?

You may be going about this the hard way since you can easily get a count of the distinct entries in a field with something like:

SELECT DISTINCT category.season as Season,
count(category.season) as 'Number per Season'
FROM
category
ORDER BY
category.season ASC

Anyway if you can post what you were really trying to do I may be able to help more.

thnx for your interest in my question, my main goal is to when a hotel is inserted automatically the (that's why the trigger) the category.num_summer or category.num_winter is updated and added one,
so i'll know how many hotels are used in Summer and how many on Winter.
A trigger that count hotels based on the season when a hotel is inserted on table location.
I hope i made clear what i want this to do. thnx again...

thnx for your interest in my question, my main goal is to when a hotel is inserted automatically the (that's why the trigger) the category.num_summer or category.num_winter is updated and added one,
so i'll know how many hotels are used in Summer and how many on Winter.
A trigger that count hotels based on the season when a hotel is inserted on table location.
I hope i made clear what i want this to do. thnx again...

OK. Then my suggestion would be this:

Add a field to the location table for season so that when the row is added to location they post if it in summer or winter. Then use the query below to find out how amny in summber and winter...

SELECT location.season as Season,
count(location.season) as 'Number per Season'
FROM
location
GROUP BY
location.season
ORDER BY
location.season ASC

and you should get a accurate count.

i want this to be done automatically not by hand, otherwise
i wouldn't post the thread,if you know a way to help me make the trigger work...

Hello,

Ok if you are running the trigger from another table (such as location) then you still need a field to hold the season and then the trigger you have will work with the addition of a where clause to tell mysql what record in category to update. If you add the season field to location table the following trigger used as an after insert trigger on location will update the first record in category every time a location is added.

Does that make sense?

i solved it at last...
thnx for those who were interested in helping me
with this problem..