how to add a default value to the column in a Oracle database table using trigger?-CodePudding

I have a customers table with following columns:


CREATE TABLE customers
  (
    customer_id NUMBER 
                GENERATED BY DEFAULT AS IDENTITY START WITH 320 
                PRIMARY KEY,
    name         VARCHAR2( 255 ) NOT NULL,
    address      VARCHAR2( 255 )         ,
    website      VARCHAR2( 255 )         ,
    credit_limit NUMBER( 8, 2 )          ,
    isActive VARCHAR2(20) /*values are either TRUE or FALSE(default)*/
  );

there are already many records in this table and now I wanted to write a trigger so when a new customer row is inserted, the isActive should be defaulted to FALSE.

I did something like this:

CREATE or REPLACE TRIGGER "UPDATE_ACTIVE_STATUS" BEFORE INSERT ON CUSTOMERS
FOR EACH ROW
when (NEW.CUSTOMER_ID > 0)
BEGIN
UPDATE CUSTOMERS set NEW.ISACTIVE = 'FALSE';
dbms_output.put('updated flag: ' || new.ISACTIVE);
END;

but I get the following error:

Error: SQL statement ignored  
Error: ORA-00904: NEW.ISACTIVE invalid identifier  
Error: NEW.ISACTIVE must be declared

I am just trying to set default value for isActive column before insert and unable to successfully do it. Also looking to see if I need to add any other validations.

Any help is appreciated.

CodePudding user response：

EDIT: you don't need a trigger for this task

ALTER TABLE CUSTOMER MODIFY isActive VARCHAR2(20) DEFAULT 'FALSE';

Don't try to update the same table inside trigger - you'll get a "mutating table" error. Just modify pseudorecord :NEW

CREATE or REPLACE TRIGGER "UPDATE_ACTIVE_STATUS"
BEFORE INSERT ON CUSTOMERS
FOR EACH ROW
BEGIN
    IF :NEW.CUSTOMER_ID > 0 THEN
        :NEW.ISACTIVE := 'FALSE';
    END IF;
    dbms_output.put('updated flag: ' || :new.ISACTIVE);
END;

And if you whant to use WHEN clause, remember that

The NEW and OLD keywords, when specified in the WHEN clause, are not considered bind variables, so are not preceded by a colon (:). However, you must precede NEW and OLD with a colon in all references other than the WHEN clause. So

CREATE or REPLACE TRIGGER "UPDATE_ACTIVE_STATUS"
BEFORE INSERT ON CUSTOMERS
FOR EACH ROW
WHEN (NEW.CUSTOMER_ID > 0)
BEGIN
    :NEW.ISACTIVE := 'FALSE';
    dbms_output.put('updated flag: ' || :new.ISACTIVE);
END;