It appears that sprites are started and stopped similar to a short movie being played. The get started with an activator, play, and can get stopped by an activator or stopped by reaching their end. Looking at them this way seems to be the most likely IMO, and will help understand how to use them. It will also hopefully make the descriptions in the sprite database easier to understand. A lot of these are very complicated and have a lot of if, ands, and whens. If you are making a series of activator sprites making up a logic map may help when programming what you want them to do. I do not recommend copy and pasting activators as this will transfer unwanted data to the next sprite created.

I will continue to add to this tutorial. If anyone finds info that is contrary to this post, please reply and explain what you have found. This is all theory, and this thread will be updated as more is known.

Input ID
An input ID is what a sprite uses to receive its start/stop command. When you set an ID of 0, it tells the sprite to not look for an ID. Any number past that, and the sprite will wait to see this ID # coming from another sprite before being able to be used. The other sprite must send out this number in an output ID. If you set an input ID other than zero, the sprite you are editing will wait for this ID before doing what you programmed it to do.

Output ID
An output ID is what a sprite uses to send a start/stop command to another sprite. When you set it to 0, it will not send a command to another sprite. Any number past will send out the ID # you entered. This will be received by any sprite that shares this when their input ID is set to the same number.

Start
This starts the sprite animation, activation, or whatever the sprite is intended to do. Depending on the settings, the sprite may stop when finished, or may continue until it is told to stop. Check the settings of the sprite for what it does once started. Giving a second start command to the same sprite ID in most cases will not do anything. In some cases it will re-start the sprite again.

Stop
This stops the sprite from playing. Some sprites such as those with timers will do this automatically. Others do not stop until told to stop. Giving a second stop command to a sprite that is already stopped in most cases will not do anything.

Physical contact Switches
34 Red Coin Ring
41 Bowser Battle Switch
66 P-Switch
88 Brick With P-Switch
107 ? Switch
108 Red ! Switch
110 Brick with Red ! Switch
235 Star Coin
152 Start/Stop Switch
291 Brick Block with ? Switch

Switches can start/stop IDs. Physical contact switches must be hit by Mario to be activated. They can all send out start/stop commands to other sprites. Most of them can receive input commands from other sprites as well. Timed switches will reset to the opposite command when the timer runs out.

Programmable Logic controllers
46 Spiked Ball Activator
164 Input Controller "And" (If X and Y, do Z)
165 Input Controller "Or" (IF X or Y, do Z)
166 Random Output Controller (If X, do Y or Z)
167 ID Count Up Controller
168 Zone Activation Switch
197 Tile Creator/Destroyer
286 ID Count Down Controller

These are basically switches that do not need physical contact to activate and can be programmed to do various things. They can send out multiple commands, as well as receive multiple commands. These can be very complicated, and will take some time and experience to understand and use.

This guide is really well explained. Nice Job!

I will add more detail as I find it. I am testing activators today, so I haven't gone into detail yet with them....the data may change. In fact, this tutorial will continue to change as more is found in the future.