Database Reference
In-Depth Information
Disallowing DELETE
What if our business requirements are such that data can only be added and modified
in some tables, but not deleted?
One way to handle this would be to just revoke the DELETE right on these tables
from all users (remember to also revoke DELETE from PUBLIC ), but this can also be
achieved using triggers.
A generic cancel trigger can be written as follows:
CREATE OR REPLACE FUNCTION cancel_op()
RETURNS TRIGGER AS $$
BEGIN
IF TG_WHEN = 'AFTER' THEN
RAISE EXCEPTION 'YOU ARE NOT ALLOWED TO
% ROWS IN %.%',
TG_OP,
TG_TABLE_SCHEMA, TG_TABLE_NAME;
END IF;
RAISE NOTICE '% ON ROWS IN %.% WON''T
HAPPEN',
TG_OP,
TG_TABLE_SCHEMA, TG_TABLE_NAME;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
The same trigger function can be used for both BEFORE and AFTER triggers. If you
use it as a BEFORE trigger the operation is skipped with a message, but if used as an
AFTER trigger, an ERROR is raised and the current (sub) transaction is rolled back.
It would also be easy to add logging of the delete attempts into a table in this same
trigger function to help enforce company policy—just add INSERT to a log table sim-
ilar to the previous example.
Search WWH ::




Custom Search