Server Audit
Captures: Who did (or failed to do) what command and when
Does not capture: What rows/values they touched.
Applies To: SQL Server 2008 Enterprise, SQL Server 2012+ Standard.
Azure SQL Database does have auditing, but it is a different architecture.
Change Data Capture
Captures: What values were inserted, updated or deleted
Does not capture: Who or when, SELECT
Applies To: 2008+ Enterprise
DML Trigger
Captures: Who did an INSERT, UPDATE or DELETE on a table or view, and when and what
Does not capture: SELECT
Notes: Do not fire for all statements (e.g. TRUNCATE TABLE, BULK INSERT)
Applies To: SQL Server, Azure SQL Database.
DDL Trigger - Database scope
Captures: Who CREATEd, ALTERed or DROPped objects in a database
Notes: Does not fire for all statements (e.g. DISABLE TRIGGER).
Applies To: SQL Server, Azure SQL Database.
DDL Trigger - server scope
Captures: Who CREATEd, ALTERed or DROPped objects at the server level.
Notes: Does not fire for all statements (e.g. RESTORE DATABASE).
Applies To: SQL Server.
Logon Trigger - server scope
Captures: Who logged on and when.
Applies To: SQL Server, Azure Synapse Analytics (TOCHECK).
Profiler, Server Trace, Extended Events
Captures: Commands sent to the server.
Does not capture: The results of the commands.
Applies To: SQL Server.