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.
