PL/SQL Trigger with a cursor and loop

Question

I'm very new to PL/SQL and i'm trying to have a DBMS output on the console of the total amount of cars for each brand. I've been breaking my head over this but don't seem to get out of it...

What am I doing wrong?


ERROR#
--------- -------------------------------------------------------------
3/5       PL/SQL: SQL Statement ignored
3/36      PLS-00201: identifier 'CUENTA' must be declared
4/5       PL/SQL: ORA-00904: : invalid identifier
9/5       PL/SQL: Statement ignored
9/17      PLS-00201: identifier 'TOTALMARACAS' must be declared


CREATE OR REPLACE TRIGGER t_mostrarmarcas 
AFTER INSERT OR UPDATE 
ON coches_seg_mano
FOR EACH ROW
DECLARE
    CURSOR totalmarcas IS
        SELECT matriculo,COUNT(*) INTO CUENTA
        FROM coches_seg_mano 
        GROUP BY matriculo;

    fila coches_seg_mano%rowtype;
    vmarca coches_seg_mano.marca%TYPE;
BEGIN
    FOR fila IN totalmaracas LOOP
        SELECT marca INTO vmarca FROM coches_seg_mano WHERE fila.cuenta=matriculo; 
        DBMS_OUTPUT.PUT_LINE('Para la marca '|| vmarca ||' tenemos '|| fila.cuenta ||' vehiculos');
    END LOOP;
END;

Why would you use a trigger to do this? The trigger will only execute in response to an insert or update on the table. Having a trigger write to dbms_output isn't sensible. You likely want an anonymous block or a procedure if you want the object to be persistent in the database. If you fix the compilation errors, you're likely going to hit a mutating table error at runtime when you try to cause your trigger to execute. — Justin Cave
– Justin Cave, Commented Dec 18, 2019 at 19:12
The DBMS output could be useful while trying to get the function working, but the message included is not really appropriate for that.. Unless the dev is trying to see if the execution has reached that section of code. — alexherm
– alexherm, Commented Dec 18, 2019 at 19:18

alexherm · Accepted Answer · 2019-12-18 19:06:09Z

1

You are trying to select into an undeclared variable CUENTA

You need to define CUENTA with a datatype before trying to use it in the cursor.

answered Dec 18, 2019 at 19:06

alexherm

1,3502 gold badges21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Popeye · Accepted Answer · 2019-12-19 02:40:58Z

0

INTO is not needed in cursor. Instead you can give alias to count(*). Also you dont need to declare variable fila.

Your code should look like following:

CREATE OR REPLACE TRIGGER t_mostrarmarcas 
AFTER INSERT OR UPDATE 
ON coches_seg_mano
FOR EACH ROW
DECLARE
    CURSOR totalmarcas IS
    SELECT matriculo,COUNT(*) as CUENTA -- this
    FROM coches_seg_mano 
    GROUP BY matriculo;
    -- fila coches_seg_mano%rowtype; -- not needed 
    vmarca coches_seg_mano.marca%TYPE;
BEGIN
    FOR fila IN totalmaracas LOOP
    SELECT marca INTO vmarca FROM coches_seg_mano WHERE fila.cuenta=matriculo; 
    DBMS_OUTPUT.PUT_LINE('Para la marca '|| vmarca ||' tenemos '|| fila.cuenta ||' vehiculos');
    END LOOP;
END;

Cheers!!

answered Dec 19, 2019 at 2:40

Popeye

36k4 gold badges12 silver badges31 bronze badges

3 Comments

Samuel Van Bladel Over a year ago

Hi and thanks for looking at this! Im still confused on how it knows that it needs to count the total for each Brand = Marca. Is it the select marca into vmarca from the begin statement that takes care of this ? And again thanks for replying so fast !!!!!

Samuel Van Bladel Over a year ago

Now it says that the cursor "totalmarcas" needs to be defined .... I'm lost with this

Samuel Van Bladel Over a year ago

Alright I got an output on my console !!!! But still it is not counting the total amount of cars for each brand ...

Collectives™ on Stack Overflow

PL/SQL Trigger with a cursor and loop

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related