I am trying to create a trigger with Regex to prevent tables being created with a column name (quoted or unquoted) that is a reserved word (e.g. ACCOUNT).
I have the trigger part worked out but I am struggling with the regex part. I have the following so far SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*|\(.+?,\s+)"\s*(PARTIAL|LIST|OF|RESERVED|WORDS)\s*"', 1, 1, 0, 'i') into l_ret2 from dual; Which works fine for quoted words.
If I try with unquoted words, for some reason it blocks when I try to add a primary key SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*?|\(.+?,\s+)(PRIMARY)\s', 1, 1, 0, 'i') into l_ret2 from dual; I added a negative lookahead but all that does is allow the name PRIMARY as a column name SELECT REGEXP_INSTR (v_stmt2, '(CREATE TABLE.+\(\s*?|\(.+?,\s+)\s*(PRIMARY(?!\s+KEY))', 1, 1, 0, 'i') into l_ret2 from dual;
So my question is what is wrong with my regex, or even better has someone already made a trigger to do what I want?