Does with sharing or without sharing on a Trigger Handler class affect SOQL and DML executed from the trigger?

Yes, for record sharing. with sharing makes SOQL return only records the running user can access and DML respect the user's edit access to records. without sharing ignores sharing rules. A class with no keyword inherits sharing from its caller (the trigger context is effectively system mode).

with sharing does not enforce object permissions or field-level security; use WITH USER_MODE or stripInaccessible for that.

Therefore a trigger handler declared with sharing can unexpectedly miss records (e.g. an Account the user can't see), so integration and roll-up logic often needs without sharing or inherited sharing deliberately.