To preface this, I’m not an expert - I’ve been doing a lot of reading up sponges, but I’m not an expert. So, with that out of the way, I know this is vague and somewhat unhelpful, but it depends on the sponge.
Some sponges are highly toxic, some moderately toxic, some mildly toxic, and some not toxic at all. Generally speaking though, sponges produce toxins to avoid predation and/or parasites, or (like some corals) as a form of chemical warfare to compete for territory. With these toxins, some of them are specifically anti-fish, some anti-echinoderm, some anti-parasite, some anti-coral, etc. As I understand it, some of these toxins are deadly to other marine species, and some just make the sponge taste bad or be less preferred by predators.
To address your actual questions now, though, some sponges constantly put out toxins (I know this one has been demonstrated), I’ve heard but can’t at this moment confirm that some might only put out toxins if disturbed/threatened (as you suggested in your post) and some might only be toxic if eaten (this one I find highly likely, but, again, I can’t confirm it at present), and - as mentioned above - some species aren’t toxic at all, so they don’t put out toxins period.
Whether or not a sponge’s toxins are harmful to fish, corals, etc. is - as I hinted at above - dependent on the toxins produced by the sponge and the quantity produced. To give an idea here, sea cucumber toxins (known as holothurin) are ichthyotoxins, meaning they are primarily toxic to fish. So, if a sea cucumber releases their toxins into a tank at a low dose, the fish in the tank will likely suffer (and possibly die) even though the amount of toxin is small, but other inhabitants will likely be unaffected. At a high dosage, however, these other inhabitants may suffer (and/or die) as well.
So, if a sponge produces fish or coral specific toxins, those may suffer or die even if the amount of toxin produced is small. Similarly, if the toxin produced is not a coral or fish specific toxin but the sponge produces a large amount of it, those might still suffer or die regardless.
I’ve never heard of a fish dying from just swimming by a toxic sponge (I’ve only heard of fish dying from eating highly toxic sponges in a laboratory, force-feeding setting at the moment), so I would assume that sponge toxicity is not a big concern under normal circumstances. If you happen to have a highly toxic (either in potency or quantity) sponge that is not being eaten, however, I suppose it’s possible it could pose some health concerns for your tank’s inhabitants, particularly if you don’t do regular water changes or run carbon.
Now, all of that said, I would guess that, in most circumstances, sponges in our tanks are probably either mildly toxic (probably edible but not preferred as a food) or only seriously toxic when consumed (and most tank inhabitants, being chemically sensitive, would likely know better than to eat highly toxic sponges). So, I would guess that in the overwhelming majority of cases, the sponges in our tanks are essentially perfectly reef safe and harmless (though, much like many soft corals, I suspect they would engage in manageable levels of chemical warfare for space in the tank - in that regard I guess you could argue that they are detrimental to corals). This is just a guess, but - like WheatToast - I’ve never heard of a sponge’s toxins causing issues in a tank.
Also, for anyone who’s interested, only a handful of sponges are really considered toxic to humans - they basically give an itchy, painful, swollen rash when touched (contact dermatitis), usually a few hours to days after the contact is made.
