简单说明一下遇到的问题吧
有这么一个触发器CREATE OR REPLACE TRIGGER APPR_TRI_PROV_CYXKZXX
BEFORE INSERT ON MID_PROV_CYXKZXX_TMP
FOR EACH ROW
BEGIN
SELECT
REPLACE(:OLD.CERT_CODE, '-', '')
INTO :NEW.CERT_CODE
FROM DUAL;
END APPR_TRI_PROV_CYXKZXX;
实现替换MID_PROV_CYXKZXX_TMP表CERT_CODE字段中的横杠
CERT_CODE字段有not null约束
触发器编译没有错,启用之后插入就报错了,禁用就可以插入真心求解oraclesql
有这么一个触发器CREATE OR REPLACE TRIGGER APPR_TRI_PROV_CYXKZXX
BEFORE INSERT ON MID_PROV_CYXKZXX_TMP
FOR EACH ROW
BEGIN
SELECT
REPLACE(:OLD.CERT_CODE, '-', '')
INTO :NEW.CERT_CODE
FROM DUAL;
END APPR_TRI_PROV_CYXKZXX;
实现替换MID_PROV_CYXKZXX_TMP表CERT_CODE字段中的横杠
CERT_CODE字段有not null约束
触发器编译没有错,启用之后插入就报错了,禁用就可以插入真心求解oraclesql
declare
NEWCERT_CODE varchar2(1000);
BEGIN SELECT REPLACE(:OLD.CERT_CODE, '-', '') INTO :NEWCERT_CODE FROM DUAL; END APPR_TRI_PROV_CYXKZXX;
试一下看
错了CREATE OR REPLACE TRIGGER APPR_TRI_PROV_CYXKZXX BEFORE INSERT ON MID_PROV_CYXKZXX_TMP FOR EACH ROW declare NEWCERT_CODE varchar2(1000); BEGIN SELECT REPLACE(:OLD.CERT_CODE, '-', '') INTO NEWCERT_CODE FROM DUAL; END APPR_TRI_PROV_CYXKZXX; 试一下看
你是要干嘛
SELECT
REPLACE(:new.CERT_CODE, '-', '')
INTO :NEW.CERT_CODE
FROM DUAL;试下看
这种处理干嘛还用触发器在做 应用程序里就应该处理掉
触发器是用来处理其他比较复杂的逻辑的 像这种问题 完全可以在执行sql前处理第二,insert时,只有new,delete时,只有old,update时,new和old都有第三,SELECT REPLACE(:new.CERT_CODE, '-', '') INTO :NEW.CERT_CODE FROM DUAL;
这种写法太啰嗦了,直接写成:NEW.CERT_CODE :=REPLACE(:new.CERT_CODE, '-', '')就可以了 存储过程本身就是sql,可以直接用replace函数,不需要再借助select语句