CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
Tuhin
Tuhin

Reputation: 37

ORA-01403: no data found for inserting data with trigger

I am creating following table in the SQL Developer environment in Oracle: 

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 );

Then, I am trying to create a following trigger for the table;

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;

And when I am trying to insert the following data

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');

I am getting this error

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'

Any help will much appreciated.

Upvotes: 0

Views: 542

Answers (1)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65408

You can rearrange as

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;

trying to insert into table while selecting through :new.cust_id which's not inserted yet.

Btw, there might rise a risk of mutating trigger due to selecting from the table on which the trigger was created.

Upvotes: 3

Related Questions