Cannot insert NULL into, ERROR during execution of trigger

Question

I have created a Trigger on table Customers so that every time a record is deleted from table customers this same record is inserted in table Customer_Archives with the current date as Deletion_Date.

I am have to insert a new customer into table Customers and then delete it. The record must be inserted correctly into table Customers_Archive.

Here's script I have so far:

CREATE TABLE Customer_Archives
(
  customer_id           NUMBER          NOT NULL,
  customer_first_name   VARCHAR2(50),
  customer_last_name    VARCHAR2(50)    NOT NULL,
  customer_address      VARCHAR2(255)   NOT NULL,
  customer_city         VARCHAR2(50)    NOT NULL,
  customer_state        CHAR(2)         NOT NULL,
  customer_zip          VARCHAR2(20)    NOT NULL,
  customer_phone        VARCHAR2(30)    NOT NULL,
  customer_fax          VARCHAR2(30),
  deletion_date         DATE, 
  CONSTRAINT customer_archives_pk 
    PRIMARY KEY (customer_id)
);

CREATE OR REPLACE TRIGGER Customers_before_insert
BEFORE DELETE ON Customers
FOR EACH ROW 
DECLARE 
    ar_row Customers%rowtype;
BEGIN
INSERT INTO Customer_Archives 
VALUES(ar_row.Customer_id,
    ar_row.Customer_First_Name,
    ar_row.Customer_Last_Name,
    ar_row.Customer_Address,
    ar_row.Customer_City,
    ar_row.Customer_State,
    ar_row.Customer_Zip,
    ar_row.Customer_Phone,
    ar_row.Customer_Fax, 
    sysdate());
    dbms_output.put_line('New row is added to Customers_Archive 
    Table with Customer_ID:' ||ar_row.Customer_id ||'on date:' || sysdate());
END;
/

SELECT trigger_name, status FROM user_triggers;

INSERT INTO CUSTOMERS
(customer_id, customer_first_name, customer_last_name, customer_address, 
customer_city, customer_state, customer_zip, customer_phone, customer_fax)
VALUES (27,'Sofia','Chen','8888 Cowden St.','Philadelphia','PA',
'19149',7654321234',NULL);

DELETE FROM CUSTOMERS
WHERE customer_id = 27;

When I try to delete the customer that I just inserted I get an error:

Error starting at line : 47 in command -
DELETE FROM CUSTOMERS
WHERE customer_id = 27
Error report -
ORA-01400: cannot insert NULL into ("TUG81959"."CUSTOMER_ARCHIVES"."CUSTOMER_ID")
ORA-06512: at "TUG81959.CUSTOMERS_BEFORE_INSERT", line 4
ORA-04088: error during execution of trigger 'TUG81959.CUSTOMERS_BEFORE_INSERT'

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2017-10-21T21:44:54

In your DELETE trigger you should be using the :OLD values when creating your archive record:

CREATE OR REPLACE TRIGGER CUSTOMERS_BEFORE_INSERT
  BEFORE DELETE ON CUSTOMERS
  FOR EACH ROW 
BEGIN
  INSERT INTO CUSTOMER_ARCHIVES 
    (CUSTOMER_ID,
     CUSTOMER_FIRST_NAME,
     CUSTOMER_LAST_NAME,
     CUSTOMER_ADDRESS,
     CUSTOMER_CITY,
     CUSTOMER_STATE,
     CUSTOMER_ZIP,
     CUSTOMER_PHONE,
     CUSTOMER_FAX,
     DELETION_DATE)
  VALUES
    (:OLD.CUSTOMER_ID,
     :OLD.CUSTOMER_FIRST_NAME,
     :OLD.CUSTOMER_LAST_NAME,
     :OLD.CUSTOMER_ADDRESS,
     :OLD.CUSTOMER_CITY,
     :OLD.CUSTOMER_STATE,
     :OLD.CUSTOMER_ZIP,
     :OLD.CUSTOMER_PHONE,
     :OLD.CUSTOMER_FAX, 
     SYSDATE());

  DBMS_OUTPUT.PUT_LINE('New row is added to Customers_Archive 
    Table with Customer_ID:' ||:OLD.Customer_id ||'on date:' || SYSDATE());
END;

In your original trigger you'd declared a row variable named ar_row but hadn't assigned anything to any of the fields - therefore they were all NULL. When a BEFORE trigger is invoked during a DELETE, the :OLD values have the values prior to the deletion, and the :NEW values are all NULL.

Best of luck.

Cannot insert NULL into, ERROR during execution of trigger

1 Answers