What is a trigger in SQL? Explain its purpose, how it operates, and provide examples of scenarios where triggers are beneficial. Additionally, discuss the potential drawbacks of using triggers in a database environment.
The interviewer expects a clear explanation of triggers, including their types (BEFORE, AFTER, INSTEAD OF), how they are defined, and common use cases such as enforcing business rules, maintaining audit logs, and automating system tasks.