You could use a try/catch block to collect the error and write it to a text file.
But my guess is that SQL Server authentication is failing. Since you are not specifying a username/password, I'm guessing that Invoke-SqlServerQuery is using Windows authentication. When you run the script as yourself, your AD account is authorized to query that database so it succeeds. When the script runs as a trigger action, it is running under local system context. This means it accesses SQL Server as the machine account, which probably doesn't have access to your database.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.