Is there a built-in way such that when a new record gets inserted, the last record gets deleted ?

I don't want more than 100 rows in a table. So when a new row is being inserted, I would like to flush out the last one from the table.

Basically, this data is coming from an incoming feed, and I dont want the database filled with all those data constantly.

If you have an auto_increment field on the table you can remove entries that are equal to 100 less than the new id. This could be performed inside a trigger using the last_insert_id() function which gives you the latest auto increment identifier.

I don't want to have an auto increment field as there are like 1000 rows being inserted every hour, which may result in overflow of max length of the auto_increment ID.

Is there a date/time field to determine the order in which the records arrive? If there is a date time stamp then the records to remove can be identified with the following select:

SELECT * FROM tablename ORDER BY datefield DESC LIMIT 100,1;
Using the date field from this query you can then use this to remove all entries older than this date/time.

using an INTEGER auto_increment column, with 1000 rows arriving per hour, guess how long before the overflow?

if you do the math, you will discover that it will take 245 years

if the column is UNSIGNED it will take 490 years

so how realistic is your concern about overflow? i would say not realistic at all

there has never, in the history of computer systems on this planet, been an application that has lasted that long

yours will ~not~ be the first

and of course, just in case, there's always UNSIGNED BIGINT

Im thinking of using SQLite for this instead.
The data is coming in via Twitter's Streaming API which is a never ending script.
Yes, and the data coming in is date sorted anyway.

Is it better to write a cron to delete entries above 100 or create a trigger to delete last row on new inserts ?

This is something you have to decide. One way you are basically guaranteeing to have only 100 rows in the table at any single time, the other your table may grow as entries arrive.

One that that has not been mentioned previously in the posts concerns any indexes that you may have on the table where you are going to store the information. Inserts and deletes causes the indexes to become inefficient over time. You should also consider rebuilding these indexes to keep them optimized for access.