时间戳

首页 > 解决方案 > ORA-01403: 找不到用于使用触发器插入数据的数据

oracle - ORA-01403: 找不到用于使用触发器插入数据的数据

问题描述

我在 Oracle 的 SQL Developer 环境中创建下表：

CREATE TABLE tbl_cust (
    cust_id         VARCHAR2(4) NOT NULL,
    cust_name       VARCHAR2(50),
    created_date    DATE DEFAULT sysdate,
    trusted         CHAR(1) DEFAULT 'N',
    ind_id          VARCHAR2(4) NOT NULL,
    emp_id          VARCHAR2(4) NOT NULL,
    cust_scont_id   VARCHAR2(4),
    cust_pcont_id   VARCHAR2(4) NOT NULL
);

ALTER TABLE tbl_cust
    ADD CHECK ( trusted IN (
        'N',
        'Y'
    ) );

CREATE UNIQUE INDEX tbl_cust__idx ON
    tbl_cust (
        cust_pcont_id
    ASC );

ALTER TABLE tbl_cust ADD CONSTRAINT tbl_cust_pk PRIMARY KEY ( cust_id );

然后，我正在尝试为表创建以下触发器；

CREATE OR REPLACE TRIGGER TRG_TRUSTED_CUSTOMER
    BEFORE INSERT OR UPDATE 
    ON tbl_cust 
    FOR EACH ROW 

DECLARE
    v_no_of_days number;

BEGIN
    IF (:new.cust_id is not null AND UPPER(:NEW.TRUSTED) = 'Y')  THEN
    --SELECT to_number(to_date(sysdate, 'dd-mm-yyyy') - to_date(c.created_date, 'dd-mm-yyyy')) INTO v_no_of_days
    SELECT to_number(to_date(TRUNC(sysdate), 'dd-mm-yyyy') - to_date(c.created_date, 'dd-mm-yyyy')) INTO v_no_of_days
    FROM tbl_cust c
    WHERE c.cust_id = :new.cust_id;
            IF (v_no_of_days  <= 365)   THEN
            raise_application_error(-20022, 'The customer is not trusted yet');
            END IF;
    END IF;
END;

当我尝试插入以下数据时

INSERT INTO tbl_cust(cust_id, cust_name, created_date, trusted, ind_id, emp_id, cust_scont_id, cust_pcont_id) 
VALUES ('C500', 'GOODMAN', '01-DEC-19', 'Y', 'IN03', 'E005','SA05', 'PA05');

我收到此错误

Error starting at line : 26 in command -
INSERT INTO tbl_cust(cust_id, cust_name, created_date, trusted, ind_id, emp_id, cust_scont_id, cust_pcont_id) 
VALUES ('C500', 'GOODMAN', '01-DEC-19', 'Y', 'IN03', 'E005','SA05', 'PA05')
Error report -
ORA-01403: no data found
ORA-06512: at "TTAPADAR.TRG_TRUSTED_CUSTOMER", line 7
ORA-04088: error during execution of trigger 'TTAPADAR.TRG_TRUSTED_CUSTOMER'

任何帮助将不胜感激。

标签： oracleplsqldatabase-trigger

解决方案

您可以重新排列为

CREATE OR REPLACE TRIGGER TRG_TRUSTED_CUSTOMER
  BEFORE INSERT OR UPDATE ON tbl_cust
  FOR EACH ROW

DECLARE
  v_no_of_days number;

BEGIN
  IF (:new.cust_id is not null AND UPPER(:NEW.TRUSTED) = 'Y') THEN
        v_no_of_days := trunc(sysdate) - :new.created_date;
  END IF;

  IF (v_no_of_days <= 365) THEN
      raise_application_error(-20022, 'The customer is not trusted yet');
  END IF;  
END;

尝试插入表格，同时选择:new.cust_id尚未插入的表格。

顺便说一句，由于从创建触发器的表中进行选择，可能会增加触发器变异的风险。

推荐阅读