It never asks for locally created applications. It's purely for things downloaded over the Internet. In essence, it's a more advanced version of the original "You just downloaded this, are you sure you want to run it" warnings from the file quarantine system introduced with....Leopard, I think?