Oracle - Fetch returns more than requested number of rows - using triggers

Question

So I am trying to use triggers to basically set some rules.. If anyone has an ID number lower than 3, he will have to pay only 100 dollars, but if someone has an ID above that, he will have to pay more. I did some research and have been told to use triggers and that triggers are very useful when fetching multiple rows. So I tried doing that but it didn't work. Basically the trigger gets created but then when i try to add values, I get the following error:-

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "S.PRICTICKET", line 6
ORA-04088: error during execution of trigger 'S.PRICTICKET'

here is what i did to make the trigger:-

CREATE OR REPLACE TRIGGER PRICTICKET BEFORE INSERT OR UPDATE OR DELETE ON PAYS FOR EACH ROW ENABLE
DECLARE 
V_PRICE PAYS.PRICE%TYPE;
V_ID PAYS.ID%TYPE;
V_NAME PAYS.NAME%TYPE;
BEGIN
SELECT ID,NAME INTO V_ID,V_NAME FROM PAYS;
IF INSERTING AND V_ID<3 THEN
V_PRICE:=100;
INSERT INTO PAYS(ID,NAME,PRICE) VALUES (V_ID,V_NAME,V_PRICE);
ELSIF INSERTING AND V_ID>=3 THEN
V_PRICE:=130;
INSERT INTO PAYS(ID,NAME,PRICE) VALUES (V_ID,V_NAME,V_PRICE);
END IF;
END;

and the thing is, when i execute this code, i actually do get a message saying the trigger has been compiled. but when when i try to insert values into the table by using the following code, i get the error message I mentioned above.

INSERT INTO PAYS(ID,NAME) VALUES (19,'SS');

Answer 1

You're getting the error you specified, ORA-01422, because you're returning more than one row with the following SELECT:

SELECT ID,NAME INTO V_ID,V_NAME FROM PAYS;

You need to restrict the result set. For example, I'll use the :NEW psuedorecord to grab the row's new ID value, which if unique, will restrict the SELECT to one row:

SELECT ID,NAME INTO V_ID,V_NAME FROM PAYS WHERE ID = :NEW.ID;

Here is the Oracle docs on using triggers: https://docs.oracle.com/database/121/TDDDG/tdddg_triggers.htm#TDDDG99934

However, I believe your trigger has other issues, please see my comments and we can discuss.

EDIT: Based on our discussion.

ORA-04088: error during execution of trigger

Using INSERT inside a BEFORE INSERT trigger on the same table will create an infinite loop. Please consider using an AFTER INSERT and change your INSERTS to UPDATES, or an INSTEAD OF INSERT.

Additionally, remove DELETE from the trigger definition. That makes no sense in this context.

Answer 2

Let's begin clearing up a few things. You were told "triggers are very useful when fetching multiple rows" this is, as a general rule and without additional context, false. There are 4 types of DML triggers: Before Statement - fires 1 time for the statement regardless of the number of rows processed. Before Row - fires once for each row processed during the statement before old and new values are merged into a single set of values. At this point you are allowed to change the values in the columns. After Row - fires once for row processed during the statement after merging old and new values into a single set of values. At this point you cannot change the column values. After statement - fires once for the statement regardless of the number of rows processed.

Keep in mind that the trigger is effectively part of the statement.

A trigger can be fired for Insert, Update, or Delete. But, there is no need to fire on each. In this case as suggested, remove the Delete. But also the Update as your trigger is not doing anything with it. (NOTE: there are compound triggers, but they contain segments for each of the above).

In general a trigger cannot reference the table that it is fired upon. See error ORA-04091.

If you're firing a trigger on an Insert it cannot do an insert into that same table (also see ORA-04091) and even if you get around that the Insert would fire the trigger, creating a recursive and perhaps a never ending loop - that would happen here.

Use :New.column_name and :Old.column_name as appropriate to refer to column values. Do not attempt to select them.

Since you are attempting to determine the value of a column you must use a Before trigger.

So applying this to your trigger the result becomes:

CREATE OR REPLACE TRIGGER PRICTICKET 
    BEFORE INSERT ON PAYS 
    FOR EACH ROW ENABLE
BEGIN

    if :new.id is not null
       if :new.ID<3 then
          :new.Price :=100;
       else 
          :new.Price := 130;
       end if ;
    else
       null; -- what should happen here? 
    end if ; 
END PRICTICKET ;