Handle database exceptions on record deletion
I have a Hotwire view where I want to prevent rows with certain properties from being edited (inserted, updated, deleted) by the users, which I'm doing with an INSTEAD OF trigger that carries out a check and abandons the update if the row has the property. I cannot easily put the protective trigger on the underlying table because I do need those rows to be editable by things other than Hotwire.
At the moment I have the trigger raise an exception if an operation is attempted on a row that is protected. This works well for updates and inserts; the user gets a nicely formatted error message in the status bar with the text of the exception. But on deletion attempts on a protected row I get an exception from the PHP because the exception from the database isn't handled:
Warning: pg_query(): Query failed: ERROR: Cannot edit Current Chemist role by hand CONTEXT: PL/pgSQL function hotwire3.it_accounts_people_roles() line 9 at RAISE in /var/www/hotwire3/dev/chem/dbdev/chemistry/classes/dbList.php on line 419
Call Stack
# Time Memory Function Location
1 0.0001 378048 {main}( ) .../list.php:0
2 0.2733 619280 htmlBase->echoHead( ) .../list.php:36
3 0.2734 619376 dbMetaList->prepareHead( ) .../htmlBase.php:402
4 0.2735 620400 dbList->__construct( $obj = ???, $manualview = ???, $searchfield = ???, $searchval = ??? ) .../dbMetaList.php:149
5 0.2828 670040 dbList->dbDelete( ) .../dbList.php:173
6 0.2829 670152 pg_query( $connection = class PgSql\Connection { }, $query = 'DELETE FROM hotwire3."10_View/IT_Accounts/People_and_IT_Roles" WHERE id = \'54873\'' ) .../dbList.php:419
( ! ) Deprecated: pg_last_error(): Automatic fetching of PostgreSQL connection is deprecated in /var/www/hotwire3/dev/chem/dbdev/chemistry/classes/dbList.php on line 426
Call Stack
# Time Memory Function Location
1 0.0001 378048 {main}( ) .../list.php:0
2 0.2733 619280 htmlBase->echoHead( ) .../list.php:36
3 0.2734 619376 dbMetaList->prepareHead( ) .../htmlBase.php:402
4 0.2735 620400 dbList->__construct( $obj = ???, $manualview = ???, $searchfield = ???, $searchval = ??? ) .../dbMetaList.php:149
5 0.2828 670040 dbList->dbDelete( ) .../dbList.php:173
6 0.2886 670464 pg_last_error( ) .../dbList.php:426
I've experimented with having the trigger return NULL instead of throwing an exception. Deletion then silently fails, which is as expected but confusing for users, and inserts then throw a PHP exception. I don't think that's the right approach anyway so have not pursued that one further.