Help with Delete Trigger
Will Matthews III asked how to turn DELETE on a table into an update of a delete flag using a trigger on Informix 12.10. Luis Marques said it is not possible, since INSTEAD OF triggers are only for views. Will used a second table filled by a delete trigger. Art Kagel suggested renaming the table and replacing it with a view with an INSTEAD OF trigger.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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.