Oracle Trigger to persist value

Question

I have a column that I need to be able to guarantee never gets set to anything other than "N" - I thought a trigger would be the perfect solution for this, but I can't seem to figure out how to make it so that anytime the column gets set to something other than "N" I reset it back to "N"

Any pointers?

EDIT: I wouldn't want to do a constraint because the application that will potentially change it to Y is outside of my control and I don't want it to be getting errors when it sets it to Y, I just want to passively set it back to N without fanfare.

Answer 1

I would recommend a column constraint instead of a trigger. Easier to track down and debug. But I assume that you mean it could never be set to any value other than N or null, right? If not, why bother storing it if the value is always 'N'?

Answer 2

The easiest way (if the option is open to you to create views) is to provide a view on your table, and set the value of the column in question to a static value of "N" within the view - i.e. persist the value in the view.

Answer 3

Something like this perhaps. I know it is a bad idea, and it will come back to haunt you. Also, I have not done Oracle in a few years, but this should get you started:

create or replace trigger trg_special_col before update on AnnoyingTable
for each row
begin
  :new.special_col := 'N';
end;