Is there a way to have a trigger campaigns call an API script?
If so, how?
If not, what are the other options?
I was thinking of using Webhooks, but how do I determine the URL of an SQL server database?
What we want?
when a new lead created in Marketo, we need to send the data immediately to Sitecore (or an SQL Server database).
"the URL of an SQL server database" doesn't exist.
Webhooks call HTTP endpoints, which could be RESTful, RESTish, SOAP, or any custom protocol you want (well, as long as you return JSON or XML if you need to update lead fields in Marketo). But they must speak HTTP.
If you build a webhook that integrates with your SQL database, or use an existing HTTP-to-SQL CRUD gateway (this pretty much what oData is, FYI) then you can have Marketo call the webhook.