Deletion of an SQL entries

Hi guys,
Could somebody advise if have encountered something similar, how could I ensure that upon entry deletion in UI the entry is also being deleted in the SQL database instead of setting just ‘deleted’=“1”, I am talking about AOS_Invoices table.
I noticed that upon invoice deletion in the UI it is not being actually deleted in the database. I tried to set up the following trigger:

CREATE TRIGGER Delete SQL entry in AOS_Invoices if 'deleted=1'
AFTER UPDATE ON aos_invoices FOR EACH ROW DELETE FROM aos_invoices
WHERE aos_invoices.deleted=“1”

But in the log, I see the following error: MySQL error 1442: Can’t update table ‘aos_invoices’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Maybe someone knows how am I supposed to set up the trigger so that SQL invoice entry is deleted right after invoice deletion in the frontend?

This is quite necessary because I’ve set a custom field to be unique in the table, and if the deleted invoice perists in the database with the value ‘deleted’=1 the system would not allow creation of a new invoice with the same custom field’s value as in prevoisly “deleted” invoice which is not visible in frontend but exists in the SQL table.

Thanks a lot in advance!

Hi,
You can do a After Save Logic hook and can do the DELETED value comparison for now and for the retrieved value. From there you can easily know that the record was recently marked as DELETED. So, if that was deleted. You can then run a Query to fully delete this record from Database.
This is for you if you are a programmer. Else you have to hire someone.
Thanks

The invoices probably have a number of other records from other tables associated to them. If you just delete a record from invoices you’ll end up wit a database full of “orphan” records in other tables.

Before you do this I suggest that you study well the database so that instead of just deleting one line in the invoices table you also delete the othere records in other tables that are related to that invoice and no other record in other parts of the DB.