maxAffectedRowsAllowedInsert

This check triggers when the Max Affected Rows Allowed value is exceeded by the number of rows inserted by a SQL statement. The SQL statement is executed against the database and then rolled back.

Uses

Use this check to prevent statements from being inserted into your database if more than a specified number of rows are affected by that statement.

Run the maxAffectedRowsAllowedInsert check

To run the maxAffectedRowsAllowedInsert check, ensure you have a Liquibase Pro license, a valid url property that points to a live JDBC connection or a snapshot, and that the checks-scope property includes the changelog. The checks-scope property is set in the default properties file, environment variable, or any standard method. Then check if any searchable object types match the supplied pattern.

To set the number of rows allowed you must customize the maxAffectedRowsAllowedInsert check. Run the following command in the CLI:
```
liquibase checks customize --check-name=maxAffectedRowsAllowedInsert
```
Set the Severity to return a code of 0-4 when triggered. (options: 'INFO'|0, 'MINOR'|1, 'MAJOR'|2, 'CRITICAL'|3, 'BLOCKER'|4)? [INFO]:

Set the maximum affected rows in the CLI:

MAX_ROWS (options: positive numeric value) [50]:

Set the message that displays when this check runs.

Set 'MESSAGE' [<AFFECTED_ROWS> rows will be affected, which is more than the allowed '<THRESHOLD>' rows. The SQL statement is '<STATEMENT>' in '<CHANGESET>'.]:

Enable the maxAffectedRowsAllowedinsert check by running the following command in the CLI:
```
liquibase checks enable --check-name=maxAffectedRowsAllowedInsert
```

`maxAffectedRowsAllowedInsert` Default message

The maxAffectedRowsAllowedInsert default message is displayed below.

Checks completed validation of the changelog and found the following issues:

Check Name:         Check Affected Rows Count (MAX_ROWS)
Changeset ID:       01
Changeset Filepath: insert.sql
Check Severity:     INFO (Return code: 0)
Message:            More than the allowed '50' rows will be affected by the SQL statement 
                    '<SQL STATEMENT>' in 
                    '<CHANGESET>'

liquibase checks enable --check-name=maxAffectedRowsAllowedInsert example output

Check 'maxAffectedRowsAllowedInsert' has been enabled.
+-------------------------------------+------------+----------+----------+--------------------------------+--------------------------------+ 
| MaxAffectedRowsAllowedDelete        | changelog  | disabled | 0        | MAX_ROWS = 50                  | This checks triggers when the  |
|                                     |            |          |          | MESSAGE = <AFFECTED_ROWS> rows | Max Affected Rows Allowed      |
|                                     |            |          |          | will be affected, which is     | value is exceeded by the       |
|                                     |            |          |          | more than the allowed          | number of rows DELETED by a    |
|                                     |            |          |          | '<THRESHOLD>' rows. The SQL    | SQL statement.  The SQL        |
|                                     |            |          |          | statement is '<STATEMENT>' in  | statement is executed against  |
|                                     |            |          |          | '<CHANGESET>'.                 | the database, and then rolled  |
|                                     |            |          |          |                                | back.|
+-------------------------------------+------------+----------+----------+--------------------------------+--------------------------------+
| MaxAffectedRowsAllowedInsert        | changelog  | disabled | 0        | MAX_ROWS = 50                  | This checks triggers when the  |
|                                     |            |          |          | MESSAGE = <AFFECTED_ROWS> rows | Max Affected Rows Allowed      |
|                                     |            |          |          | will be affected, which is     | value is exceeded by the       |
|                                     |            |          |          | more than the allowed          | number of rows INSERTED by a   |
|                                     |            |          |          | '<THRESHOLD>' rows. The SQL    | SQL statement.  The SQL        |
|                                     |            |          |          | statement is '<STATEMENT>' in  | statement is executed against  |
|                                     |            |          |          | '<CHANGESET>'.                 | the database, and then rolled  |
|                                     |            |          |          |                                | back.|
+-------------------------------------+------------+----------+----------+--------------------------------+--------------------------------+
| MaxAffectedRowsAllowedUpdate        | changelog  | enabled  | 0        | MAX_ROWS = 50                  | This checks triggers when the  |
|                                     |            |          |          | MESSAGE = <AFFECTED_ROWS> rows | Max Affected Rows Allowed      |
|                                     |            |          |          | will be affected, which is     | value is exceeded by the       |
|                                     |            |          |          | more than the allowed          | number of rows UPDATED by a    |
|                                     |            |          |          | '<THRESHOLD>' rows. The SQL    | SQL statement.  The SQL        |
|                                     |            |          |          | statement is '<STATEMENT>' in  | statement is executed against  |
|                                     |            |          |          | '<CHANGESET>'.                 | the database, and then rolled  |
|                                     |            |          |          |                                | back.|
+-------------------------------------+------------+----------+----------+--------------------------------+--------------------------------+
Liquibase command 'checks enable' was executed successfully.