According to the Bible, what happens when the Spirit of the Lord comes upon somebody?
Well, the short answer is this: a Spirit-possessed person usually kills things.
The phrase ("the Spirit of the Lord came upon him") occurs only 8 times (in 7 verses) in the Bible. Let's see what happens in each case.
The Spirit of the Lord came upon Othniel and he went to war. 
And the Spirit of the LORD came upon him, and he judged Israel, and went out to war. Judges 3:10
When the Spirit of the Lord came upon Gideon, he blew a trumpet. That sounds innocent enough (even if a little crazy). But Gideon (with God's Spirit) used the trumpet to summon soldiers to war. 
The Spirit of the LORD came upon Gideon, and he blew a trumpet. Judges 6:34
Then the Spirit of the LORD came upon Jephthah ... And Jephthah vowed a vow unto the LORD, and said, If thou shalt without fail deliver the children of Ammon into mine hands, Then it shall be, that whatsoever cometh forth of the doors of my house to meet me, when I return in peace from the children of Ammon, shall surely be the LORD's, and I will offer it up for a burnt offering. ... and the LORD delivered them into his hands. And he smote them ...
And Jephthah came to Mizpeh unto his house, and, behold, his daughter came out to meet him with timbrels and with dances ... And she said unto him, My father, if thou hast opened thy mouth unto the LORD, do to me according to that which hath proceeded out of thy mouth. And ... her father ... did with her according to his vow which he had vowed. Judges 11:29-39
Behold, a young lion roared against him. And the Spirit of the LORD came mightily upon him, and he rent him as he would have rent a kid. Judges 14:5-6
And the Spirit of the LORD came upon him ... and slew thirty men. Judges 14:19
And the Spirit of the LORD came mightily upon him ... And he found a new jawbone of an ass ... and slew a thousand men therewith. Judges 15:14-15
The Spirit of God came upon Saul and he took two oxen and hacked them into pieces, sending the pieces throughout Israel, with this message: "Whoever doesn't come to Saul will have this happen to their oxen."
And the Spirit of God came upon Saul ... and he took a yoke of oxen, and hewed them in pieces, and sent them throughout all the coasts of Israel by the hands of messengers, saying, Whosoever cometh not forth after Saul and after Samuel, so shall it be done unto his oxen. 1 Samuel 11:6-7
The Spirit of God continued to be on Saul until he refused to kill all the Amalekites as commanded by God in 1 Samuel 15:2-3. For refusing to commit genocide, God took away his Spirit from Saul, sicked "an evil spirit from the Lord" on him, and transferred his Spirit over to David, where it remained forever.
The Spirit of the LORD came upon David from that day forward. But the Spirit of the LORD departed from Saul, and an evil spirit from the LORD troubled him. 1 Samuel 16:13-14