`in_transaction` macro for pre- / post-hooks

### Contributions

- [X] I have read the contribution docs, and understand what's expected of me.

### Link to the page on docs.getdbt.com requiring updates

https://docs.getdbt.com/reference/resource-configs/pre-hook-post-hook#transaction-behavior

### What part(s) of the page would you like to see updated?

dbt has a macro named [`in_transaction`](https://github.com/dbt-labs/dbt-adapters/blob/10e6d59e1cb6cc53c4e92ab37dac817641f66f2f/dbt/include/global_project/macros/materializations/hooks.sql#L28-L30) ([#510](https://github.com/dbt-labs/dbt-core/pull/510)). Handy!

It didn't show up in a search in our docs though:

You can use it (along with its siblings [`before_begin` and `after_commit `](https://docs.getdbt.com/reference/resource-configs/pre-hook-post-hook#transaction-behavior)) to add arbitrary user-defined statements in 4 different places:

1. Before the main materialization code but **not** within the same transaction
2. Before the main materialization code and **within** the same transaction
3. After the main materialization code and **within** the same transaction
4. After the main materialization code but **not** within the same transaction

### Example

```sql
{{ config(
pre_hook=[
before_begin("select 1 as id"),
in_transaction("select 2 as id"),
],
post_hook=[
in_transaction("select 3 as id"),
after_commit("select 4 as id"),
]
) }}

select 1234 as id
```

That isn't the only way to do it, of course. If [using a dictionary](https://docs.getdbt.com/reference/resource-configs/pre-hook-post-hook#transaction-behavior) looks like more fun to you, knock yerself out:

```sql
{{ config(
materialized="plain",
pre_hook=[
{
"sql": "select 1 as id",
"transaction": False
},
{
"sql": "select 2 as id",
"transaction": True
}
],
post_hook=[
{
"sql": "select 3 as id",
"transaction": True
},
{
"sql": "select 4 as id",
"transaction": False
}
]

) }}

select 1234 as id
```

### Additional information

The main caveat is that not all dbt adapters support transactions. There is already a caveat for this, but I'm not sure if it's fully up-to-date or not (https://github.com/dbt-labs/docs.getdbt.com/issues/6225):

> Important note: Do not use this syntax if you are using a database where dbt does not support transactions. This includes databases like Snowflake, BigQuery, and Spark or Databricks.