CODEWITHSUNDEEP
sqloracle-databaseplsqltriggerssqlplus
AAA
AAA

Reputation: 13

"ORA-04073: column list not valid for this trigger type" Error in Trigger creation

I want to write a trigger then give an error when IDPRODUCT=3 or IDPRODUCT=9 AND QUANTITY<10. If this condition is true then it have to give "Please enter quantity greater than 10." But it is not working and give me error. Here is my trigger: This trigger is to check during insertion in table BB_BASKETITEM.

CREATE OR REPLACE TRIGGER User_defined_error
BEFORE  INSERT OF IDPRODUCT,QUANTITYON BB_BASKETITEM
FOR EACH ROW 
BEGIN
IF:NEW.IDPRODUCT=3 OR NEW.IDPRODUCT=9 AND NEW.QUANTITY<10 THEN
 DBMS_OUTPUT.PUT_LINE(' Please enter quantity greater than 10. ');
 END IF;
END;
/

Error:

Error report -
ORA-04073: column list not valid for this trigger type
04073. 00000 -  "column list not valid for this trigger type"
*Cause:    A column list was specified for a non-update trigger type.
*Action:   Remove the column list.

Upvotes: 1

Views: 787

Answers (1)

Littlefoot
Littlefoot

Reputation: 143003

As Oracle says: column list is invalid for this trigger type. Just omit it.

Sample table and trigger:

SQL> create table bb_basketitem (idproduct number, quantity number);

Table created.

SQL> create or replace trigger user_defined_error
  2    before insert on bb_basketitem
  3    for each row
  4  begin
  5    if    (:new.idproduct = 3 or :new.idproduct = 9)
  6       and :new.quantity < 10
  7    then
  8       raise_application_error(-20000, 'Please enter quantity greater than 10.');
  9    end if;
 10  end;
 11  /

Trigger created.

Testing:

SQL> insert into bb_basketitem(idproduct, quantity) values (3, 5);
insert into bb_basketitem(idproduct, quantity) values (3, 5)
            *
ERROR at line 1:
ORA-20000: Please enter quantity greater than 10.
ORA-06512: at "SCOTT.USER_DEFINED_ERROR", line 5
ORA-04088: error during execution of trigger 'SCOTT.USER_DEFINED_ERROR'


SQL> insert into bb_basketitem(idproduct, quantity) values (3, 20);

1 row created.

SQL>

Column list makes sense for before update trigger:

SQL> create or replace trigger trg_bu_bbb
  2    before update of quantity on bb_basketitem
  3    for each row
  4  begin
  5    if    (:new.idproduct = 3 or :new.idproduct = 9)
  6       and :new.quantity < 10
  7    then
  8       raise_application_error(-20001, 'Please enter quantity greater than 10.');
  9    end if;
 10  end;
 11  /

Trigger created.

SQL> update bb_basketitem set quantity = 1;
update bb_basketitem set quantity = 1
       *
ERROR at line 1:
ORA-20001: Please enter quantity greater than 10.
ORA-06512: at "SCOTT.TRG_BU_BBB", line 5
ORA-04088: error during execution of trigger 'SCOTT.TRG_BU_BBB'


SQL>

Upvotes: 1

Related Questions