2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

In SQL how do I throw an error when updating 'not null' values in a database table

Looking to throw an error where the value i"m updating is a "not null" value.

For example. In below table, say I wanted to update Paul"s lastname with "Jackson". The SQL should throw an error to say that the value that is being updated is "not null".

First Lastname
Paul Parkinson
Peter null
Turkey null

 

userimage

You need to do this with a trigger. Example trigger:

create trigger schema.trigger_name

    before update of last_name

    on tablename

    for each row

begin

    if :old.last_name is not null then

        raise_application_error (-20100, 'Last name already has a value');

    end if;

end;


But this will stop any process that is running by raising a plsql error.

 

Answer is