Is this check(nvl(column_name in ('x1','x2'), (another_column.value='something_else')) possible in oracle sql?

Question

I've got this line of SQL code here, which doesn't work. I guess it's because I'm writing the wrong syntax.

constraint ck_ceg check(nvl(ceg in ('%rt','%yrt'),'vezérigazgató'))

This check would have to check if 'ceg' wether has 'rt' or 'yrt' at the end of it's name and if it does another columns value has to be changed to 'vezérigazgató'. Is this possible by a check constraint?

Thanks in advance!

Answer 1

Check only accept booleans (Here you passing boolean or string).

To check the value inserted / updated is correct you can use :

constraint ck_ceg check(ceg like '%rt' or ceg like '%yrt' or pozicio = 'vezérigazgató')

Check is not able to update the value, it just test the validity of input.

If you want to automatically update with 'vezérigazgató' if ceg not end with rt or yrt you have to write a trigger, like that :

create or replace trigger yourtable
before insert or update on yourtable
for each row
begin
    if :new.ceg not like '%rt' or :new.ceg not like '%yrt' then
        :new.pozicio := 'vezérigazgató';
    end if;
end;