问题描述
所以过去几天我一直在尝试解决这个错误.我有一个存储过程，可以将一个条目插入到我的临时表中.一旦临时表插入了一行，就会触发一个触发器，将数据加载到其他几个表中.我的触发代码如下:
So I've been trying for the last couple days trying to trouble shoot this error. I have a stored procedure that inserts an entry into my staging table. once the staging table has a row inserted, a trigger is fired that loads the data into a couple other tables. My trigger code is below:
create or replace TRIGGER xml_load_trigger_value
AFTER INSERT
ON xml_hours_load_2
FOR EACH ROW
WHEN (
NEW.processed = 'N'
) DECLARE
Value_ID Number;
pValue_ID Number;
Calculation_ID Number;
pCalculation_ID NUMBER;
Calculation_Value_ID Number;
Hr_Utc Varchar2(4);
p_Entity_Address_ID Varchar2(50);
New_Value_ID Number;
New_Calculation_ID Number;
New_Calculation_Value_ID Number;
New_Value_Tx varchar2(50);
NEW_UTC_Offset varchar2(50);
Survey_Respondent_ID varchar2(50);
NEW_submission_id varchar2(50);
--PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Value_ID := value_value_id_seq.NEXTVAL;
New_Value_ID := Value_id;
Calculation_Value_ID :=CALCULATION_VALUE_CALCULATI329.NEXTVAL;
calculation_id := Calculation_Calculation_ID_SEQ.NEXTVAL;
Insert into Value
(VALUE_ID, PRODUCT_ID, DATA_SOURCE_ID, UNIT_CD, VALUE_TX, UTC_OFFSET, DATA_DATE, HR_UTC, HR, HR_NUM, DATA_CODE, CREATE_DT, CREATE_USER_ID)
VALUES
(Value_ID, '109' , '269', 'NA', :NEW.Value_Tx, :NEW.UTC_Offset, :NEW.Data_Date, :NEW.hr_utc, :NEW.hr, :NEW.hr_num, :NEW.data_code, SYSDATE, '15');
--
Insert into Submission
(SURVEY_RESPONDENT_ID, SUBMISSION_DT, SUBMISSION_TYPE_ID, SUBMISSION_NAME_TX, SUBMISSION_SEQ_NB, CREATE_DT, CREATE_USER_ID, MODIFY_DT, MODIFY_USER_ID, EFFECTIVE_DT, INACTIVE_DT)
VALUES
('2527451', :NEW.Data_Date, '1', '20190418', '0', sysdate, '1', null, null, null, null);
--
Insert into Submission_Value
(Submission_ID, Value_ID, Form_Field_Id, Create_DT, Create_user_id, Modify_DT, Modify_user_id, Effective_Dt, Inactive_DT)
VALUES
((Select Submission_ID from Submission where Survey_respondent_Id = '2527451' and rownum = 1), Value_Id, '10466', sysdate, '930', null, null, null, null);
END;
推荐答案
错误信息很明确::NEW.Value_Tx is null and your table Value has aVALUE_TX 列上的 NOT NULL 约束.所以这是 Oracle 正常工作，强制执行在表上定义的数据完整性规则.
The error message is very clear: :NEW.Value_Tx is null and your table Value has a NOT NULL constraint on the column VALUE_TX. So this is Oracle working correctly, enforcing the data integrity rules defined on the table.
所以你需要做的是弄清楚为什么 :NEW.Value_Tx 为空.它为空，因为它在您插入 xml_hours_load_2 的行中为空.这就是你需要修复的.
So what you need to do is figure out why :NEW.Value_Tx is null. It's null because it's null in the row you're inserting into xml_hours_load_2. That's what you need to fix.
如果事实证明触发中的空值有合理的原因，那么您需要实施一些解决方法.也许您需要传递一个硬编码的值.也许您需要生成一个值(也许是一个序列).业务规则应该为此指定逻辑.
If it turns out there's a legitimate reason for the null value in the triggering then you need to implement some workaround. Perhaps you need to pass a hard-coded value. Perhaps you need to generate a value (maybe with a sequence). The business rules ought to specify the logic for this.
这篇关于为什么我收到这个错误?"不能将空值插入 'VALUE.VALUE_TX'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！