Menu
I would like to disable the DELETE statement on a table. What I need to do is a SET a field value instead of removing the respective record.
So far I have tried the following:
CREATE TRIGGER delete_trg
INSTEAD OF DELETE
ON schema.tbl
FOR EACH ROW
EXECUTE PROCEDURE schema.tbl_delete_fn();
My schema.tbl_delete_fn() function is as follows:
CREATE OR REPLACE FUNCTION schema.tbl_delete_fn()
RETURNS trigger AS
BEGIN
NEW.deleted := true;
RETURN NEW;
END;
So far this doesn't seem to work... any ideas?
946
INSTEAD OF DELETE TRIGGERS are used, to delete records from a view. Introduction. INSTEAD OF DELETE triggers are used to delete records from a View that is based on multiple tables. Description. An INSTEAD OF DELETE trigger gets executed in place of the DELETE event on a table or a View.
INSTEAD OF triggers do not support WHEN conditions. Typically, row-level BEFORE triggers are used for checking or modifying the data that will be inserted or updated. For example, a BEFORE trigger might be used to insert the current time into a timestamp column, or to check that two elements of the row are consistent.
TG_OP. Data type text ; a string of INSERT , UPDATE , DELETE , or TRUNCATE telling for which operation the trigger was fired. TG_RELID. Data type oid ; the object ID of the table that caused the trigger invocation. TG_RELNAME.
You want a BEFORE DELETE trigger whose function returns NULL and the row variable is OLD, not NEW.
CREATE TRIGGER delete_trg
BEFORE DELETE
ON schema.tbl
FOR EACH ROW
EXECUTE PROCEDURE schema.tbl_delete_fn();
CREATE OR REPLACE FUNCTION schema.tbl_delete_fn()
RETURNS trigger AS '
BEGIN
UPDATE schema.tbl SET deleted=true WHERE ctid=OLD.ctid;
RETURN NULL;
END; ' language plpgsql;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
