1

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!

Narc0t1CYM
  • 499
  • 6
  • 25
  • 1
    A constraint can only reject a row. I cannot change a value. Use triggers instead. Furthermore, your string test should use `like`; otherwise the percent sign have no effect. – Codo May 11 '16 at 19:37

1 Answers1

2

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;
ebahi
  • 536
  • 2
  • 7
  • the last part of your code is not what I need. The end should be like: constraint ck_ceg check(ceg like '%rt' or ceg like '%yrt' or pozicio = 'vezérigazgató') which is a different column. – Narc0t1CYM May 11 '16 at 19:43
  • Ok, do you want to just check the values or make an update ? – ebahi May 11 '16 at 19:46
  • Both. To be honest, I'm not quite sure because this is one of the exercises of my database systems subject, and the description of the exercise is quite blurry. – Narc0t1CYM May 11 '16 at 19:48