sql insert and update question..multiple queries in one statement

Question

I have a table called auctions, which has various columns such as username, auction id(the primary key), firstname, lastname, location etc, as well as a category column. The category column is blank by default, unless it is filled in for a particular record by a user.

I have made a new users table, which has username and category columns, as well as aditional fields which will be completed by user input.

I would like to know if it is possible when updating a record in the auctions table to have a category, to insert the username and category from that record into the users table as long as the username is not already present in the table.

For example, if I have the following tables:

auctions

auctionid   username firstname lastname category
------------------------------------------------------------------------
1    zerocool john  henry  
2    fredflint fred  smith  
3    azazal  mike  cutter

Then, upon updating the second record to have a catagory like so:

2    fredflintsoner fred  smith  shoes

The resulting users table should be:

users

username    shoes pants belts misc1 misc2
--------------------------------------------------
fredflint   true

With no record have existed previously.

If additional auctions exist with the same username in the auctions table, such as:

7    fredflint fred  smith belts

Then even if this auction is added to the category, a new record should not be inserted for the users table, as the username is already , however it should be updated as necessary, resulting in:

username    shoes pants belts misc1 misc2
--------------------------------------------------
fredflint   true  true

Answer 1

What you are looking for is known as a TRIGGER. You can specify something to run after every insert/update in the auctions table and then determine what to do to the users table.

Answer 2

A couple of questions come to mind. The first is, your user table looks denormalized. What happens when you add a new category? Consider a user table in the form of:

id username category

Where you have multiple rows if a user has multiple categories:

1 fredflint shoes
2 fredflint pants
....

The second question I have is, why do you need a user table at all? It looks like all the information in the user table is already stored in the auction table! You can retrieve the user table simply by:

select distinct username, category
from auctions

If you need the separate table, an option to manually update the table when you create a new auction. I'd do it like this (I know just enough about triggers to avoid them):

1 - Make sure there's a row for this user

   if not exists (select * from users where username = 'fredflint')
       insert into users (username) values ('fredflint')

2 - Make sure he the shoe category

   if not exists (select * from users where username = 'fredflint' and shoes = 1)
       update users set shoes = 1 where username = 'fredflint'