Create a trigger in SQL that doesn't allow (or deletes) certain special characters like: $, @, ?, #, & and etc

Question

I've been trying to create a trigger that when receives an special character either doesn't allow the insert or delete the character, this being aplied to characters like (!, @, $, %, &, *, ?, <, >, -).

I really could not think in any trigger function that would work for this situation, maybe replace every special character by ''...but I think would not be the best solution.

Anyone have any idea of code to use in this situation?

thanks in advance for the help.

Answer 1

One option is to create check constraint (not trigger).

SQL> CREATE TABLE test
  2  (
  3     name   VARCHAR2 (30)
  4              CONSTRAINT ch_name CHECK
  5                 (NOT REGEXP_LIKE (name, '!|@|\$|\%|&|\*|\?|<|>|-'))
  6  );

Table created.

Testing:

SQL> INSERT INTO test (name) VALUES ('L!t');
INSERT INTO test (name) VALUES ('L!t')
*
ERROR at line 1:
ORA-02290: check constraint (ZAHTJEV.CH_NAME) violated


SQL> INSERT INTO test (name) VALUES ('L@t');
INSERT INTO test (name) VALUES ('L@t')
*
ERROR at line 1:
ORA-02290: check constraint (ZAHTJEV.CH_NAME) violated


SQL> INSERT INTO test (name) VALUES ('L?t');
INSERT INTO test (name) VALUES ('L?t')
*
ERROR at line 1:
ORA-02290: check constraint (ZAHTJEV.CH_NAME) violated


SQL> INSERT INTO test (name) VALUES ('Lit');

1 row created.

SQL>

---

If you want to remove those characters, then use a trigger:

SQL> CREATE TABLE test
  2  (
  3     name   VARCHAR2 (30)
  4  );

Table created.

SQL> CREATE OR REPLACE TRIGGER trg_biu_test
  2     BEFORE INSERT OR UPDATE
  3     ON test
  4     FOR EACH ROW
  5  BEGIN
  6     :new.name := REGEXP_REPLACE (:new.name, '!|@|\$|\%|&|\*|\?|<|>|-', '');
  7  END;
  8  /

Trigger created.

SQL> INSERT INTO test (name) VALUES ('L@$%*?t');

1 row created.

SQL> SELECT * FROM test;

NAME
----
Lt

SQL>