I have data as below in csv format and uploading this data into table:

TABLE NAME: MYDATA

 ---- ------- ------- --------- 
| ID | NAME  | STATE |  CODE   |
 ---- ------- ------- --------- 
|  1 | P&P   | AQ    | BIN1234 |
|  2 | ABC   | AQ    | BIN5678 |
|  3 | G-I   | AQ    | BIN3457 |
|  4 | MC-DO | AQ    | BIN3462 |
|  5 | TEC   | AQ    | ERP9756 |
|  6 | CBA   | BT    | ERP4353 |
|  7 | W&X   | BT    | ERP5456 |
|  8 | P-Q   | GH    | ERP3457 |
 ---- ------- ------- --------- 

Trying to achieve below conditions based on data:

1. Delete values from STATE column where value is not equal to 'AQ'.
2. Delete values from CODE column where value like '%ERP%'.
3. Replace special characters AMPERSAND '&' and HYPHEN '-' with UNDERSCORE '_' from NAME column.
4. Trim whitespaces from NAME column.

Wrote below trigger but getting error:

CREATE OR replace TRIGGER BI_MYDATA_TR
  BEFORE INSERT OR UPDATE ON MYDATA
BEGIN
    DELETE FROM MYDATA
    WHERE  upper(state) <> 'AQ'
    OR upper(code) LIKE '%ERP%'; 

    :NEW.name := trim(regexp_replace(:NEW.name, '&|-', '_'));
END; 

Below is the error I am getting:

Error at line 3: PL/SQL: Statement ignored Error at line 3: PLS-00201: identifier 'NEW.NAME' must be declared

1. create or replace trigger BI_MYDATA_TR
2. before insert or update on MYDATA
3. begin
4. DELETE FROM MYDATA WHERE upper(state) <> 'AQ' OR upper(code) LIKE '%ERP%';
5. :NEW.name := trim(regexp_replace(:NEW.name, '&|-', '_'));

Appreciate if I get any help on how to combine these multiple conditions in one trigger.

Thanks in advance.

Thanks,

Richa

CodePudding user response：

It looks like there is no column NAME in your table.

In addition, your requirement is

Delete values from STATE column where value is not equal to 'AQ'.

Does it mean that you want to delete the value from the triggered row? If it does, then your logic of "DELETE FROM MYDATA where ..." is incorrect - it will delete rows, not values from the triggered row. The correct way is

if upper(:new.state) <> 'AQ' then 
   :new.state := null;
end if;

CodePudding user response：

After BEFORE INSERT OR UPDATE ON MYDATA
i will put 
  REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin .....