Help with Delete Trigger

I need to write a trigger and procedure so that if a user sends a DELETE for a record in a table (not a view) then instead of deleting the record the action will update a field (a delete flag we're using) in the record. I have used the "CREATE TRIGGER xxx INSTEAD OF DELETE ON tablename" for views succesfully, but can't figure out how to do the same for a table. We are using Informix 12.10 for the databases, does anyone know a query example I could use to do this? Right now the action request comes in like: DELETE FROM invmas WHERE inv_stock_code = 'xxyyzz';and I need it to instead do: UPDATE invmas SET inv_delete='Y' WHERE inv_stock_code = 'xxyyzz';Anyone possibly have any advice?

I don't think it is possible in Informix. To the best of my knowledge, the trigger cannot prevent the DELETE from removing the rows in the table. The INSTEAD OF TRIGGER for VIEWS that you already used seems to be the only provided mechanic for replacing an action on a "table" for something else completely different. You could try a trigger to insert a new row with the updated values, but the manual warns that it can lead to inconsistent results, plus other issues that could arise.

Thanks. I thought that was the case from my own research, just was a small hope someone might know a different way. What I ended up doing, was making a 2nd table for the deleted items, and put in a trigger so it inserts the data into the 2nd table when it is deleting from the first table.

The best solution may be to rename the table and replace it with a view that has an INSTEAD OF trigger to catch the delete and mark it deleted. This has the advantage the the view can prevent users from seeing deleted rows by including a filter on the delete flag in the view definition.