A Custom Workflow Activity Plugin is very similar to a Plugin exempt that it is executed from within a CRM Workflow, Whereas Plugins Are executed on Events that Occur on the Entity, So workflow Activities give you more control when the plugin will be executed as well as allowing you to configure some logic through the interface whereas a Custom Plugin has to be completely configured in Visual Studio and Plugin registration tool. Codeplex is full of usefull Custom Workflow Activities allowing you to overcome the limitations in CRM, so they are a create way for extending CRM for specific scenarios using Workflows.
wgen we are using Plugin we have a number of triggers availble(called as Steps/Message) to trigger the pluggin execusion,
where Custom workflows are triggered by standard workflows available in CRM,
that make the custom workflow triggers limited(Create, Updat, StatusChange, Delete and On assign).
So plugins have higher flexibility than workflows which makes it suitable for highly complex logics.
Where there are several opinion about this topic, i would state my thoughts as below,
Workflows are better when you need the operation needs to be configured periodically by non programmer administrator, where you can implement the configuration part as standard workflow (Workflow1), and static part of the functionality as custom workflow which will be executed by the standard workflow (workflow1).
Custom workflows has limited triggers when comparing to those of plugins, this is because Custom workflows need to be called from an OOB which can be triggered on create,update,delete or assign.
Plugins are richer in feature in terms of data bindings, triggers, execution ordering, which makes very high level of control over the operation and flexibility.
So in conclusion i would say if your scenario is not have big complexity in terms of triggers and operating modes or it needs some sort of reconfiguration needed by non programmers periodically go for Workflow/custom workflow else go for Plugins.