What is cursor in SQL and when to use

A cursor is basically a table, generated from a query, so you can iterate through the rows. It is usually used in a stored procedure or trigger.

A cursor is the tool we've got to iterate through records.
You can loop through the results of your query and do whatever you have to do with each record.
I've got to disagree with pritaeas on the sp or triggers part. You use a cursor when you need to handle each record differently, in a specific order where a case won't cover things. Usually they are too expensive (in terms of resources) and don't get the job done as fast as processing bulk data would.