Actually, yes, although I don't recommend it actually be done by code within a trigger. The process of writing to a file just takes relatively too long. Instead of the trigger doing the actual export to a file, the trigger should make a note in a "control" table and a job that runs on a regular basis should scan the control table to see if it has any work to do.
The job itself can run a command-line level command (CmdExec Step) that uses either BCP or SQLCMD to execute a query against the server/database to create the required output to a file. Although BCP has no actual support for a class of files known as CSV, you can create a BCP Format file that, when combined with a little T-SQL prestidigitation, will allow exquisite control over what the column-based output (sometimes called a "flat file") will look like. You could also do similar with a "Powershell" step (although my tendency is to avoid curly-brace languages).
You can also use xp_CmdShell to do the same things with even more flexibility and control over the process. Unfortunately, most DBAs and Developers have been brainwashed into believing that it's a security risk and nothing could be further from the truth if it's used properly. Best of all is that it can all be done by stored procedure, which makes code distribution to other machines/databases very simple and non-error prone due to "configuration changes". What that also means is that I don't need to expand the security surface area of the system by using SSIS or other tools and you don't need to learn or support a whole new application or language to do it properly.
As a bit of a sidebar, using single quotes to encapsulate individual elements in the output is totally non-standard. Even an import to EXCEL will require a little bit of special handling (need to identify the quoted identifier) in such cases. A part of the CSV standard is to use double-quotes consistently if such a thing is needed. If you follow the real CSV standard, only text-based columns need to be encapsulated and, unlike what most programs seem to spit out, requires the field to be encapsulated on every line in the file and not just the lines/elements that contain embedded delimiters. As a best practice, you should also make the delimiters and quoted text identifiers apply to the header (if present) as well.