英文:
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 test2 (3 name VARCHAR2 (30)4 CONSTRAINT ch_name CHECK5 (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) violatedSQL> 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) violatedSQL> 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) violatedSQL> INSERT INTO test (name) VALUES ('Lit');1 row created.SQL>
如果要删除这些字符，那么使用触发器：
SQL> CREATE TABLE test2 (3 name VARCHAR2 (30)4 );Table created.SQL> CREATE OR REPLACE TRIGGER trg_biu_test2 BEFORE INSERT OR UPDATE3 ON test4 FOR EACH ROW5 BEGIN6 :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----LtSQL>
英文:
One option is to create check constraint (not trigger).
SQL> CREATE TABLE test2 (3 name VARCHAR2 (30)4 CONSTRAINT ch_name CHECK5 (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) violatedSQL> 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) violatedSQL> 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) violatedSQL> INSERT INTO test (name) VALUES ('Lit');1 row created.SQL>
If you want to remove those characters, then use a trigger:
SQL> CREATE TABLE test2 (3 name VARCHAR2 (30)4 );Table created.SQL> CREATE OR REPLACE TRIGGER trg_biu_test2 BEFORE INSERT OR UPDATE3 ON test4 FOR EACH ROW5 BEGIN6 :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----LtSQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论