英文:
Create a trigger in SQL that doesn't allow (or deletes) certain special characters like: $, @, ?, #, & and etc
问题
我一直在尝试创建一个触发器，当接收到特殊字符时，要么不允许插入，要么删除该字符，这适用于字符（！，@，$，%，＆，*，？，<，>，-）。
我真的想不出适用于这种情况的任何触发器函数，也许将每个特殊字符替换为''...但我认为这可能不是最佳解决方案。
有没有任何在这种情况下使用的代码的想法？
在此先感谢您的帮助。
英文:
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.
答案1
得分: 1
一种选项是创建检查约束（而不是触发器）。
SQL> CREATE TABLE test
2 (
3 name VARCHAR2 (30)
4 CONSTRAINT ch_name CHECK
5 (NOT REGEXP_LIKE (name, '!|@|$|\%|&|\*|\?|<|>|-' ))
6 );
Table created.
测试：
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>
如果要删除这些字符，那么使用触发器：
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>
英文:
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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论