`Basically, i need to create something that when the user insert a value on a table, it turns into a multi insert, he will say the number of rows that will become in a part of table.

But i have 0 ideia from were begin.

My table have COMPMOV - INT: is the number of rows will return. VLRPROV - NUMBER: DTVENC - DATE: PRAZO - INT: PARCELA - INT:

i started from a another trigger that i created before, but this one i just fill a field that is blank when the user insert.`

CREATE OR replace TRIGGER "TRG_INC_USU_OBSMULTI" 
FOR INSERT OR UPDATE
    ON AD_OBSMULTI
    COMPOUND TRIGGER TYPE R_OBS_TYPE IS RECORD (
        OBS_ID AD_OBSMULTI.ID%TYPE,
        OBS_IDOBS AD_OBSMULTI.IDOBS%TYPE,
        OBS_CODUSU AD_OBSMULTI.CODUSU%TYPE
    );

TYPE T_OBS_TYPE IS TABLE OF R_OBS_TYPE INDEX BY PLS_INTEGER;

T_OBS T_OBS_TYPE;

AFTER EACH ROW IS BEGIN
    T_OBS (T_OBS.COUNT   1).OBS_IDOBS := :NEW.IDOBS;
    T_OBS (T_OBS.COUNT).OBS_ID := :NEW.ID;
    T_OBS (T_OBS.COUNT).OBS_CODUSU := :NEW.CODUSU;

END AFTER EACH ROW;

AFTER STATEMENT IS L_CODUSU AD_OBSMULTI.CODUSU%TYPE;

BEGIN
    SELECT
        STP_GET_CODUSULOGADO INTO L_CODUSU
    FROM
        DUAL;

FOR indx IN 1..T_OBS.COUNT
LOOP
    IF T_OBS(indx).CODUSU IS NULL THEN
UPDATE
    AD_OBSMULTI
SET
    CODUSU = L_CODUSU
WHERE
    ID = T_OBS(indx).OBS_ID
    AND IDOBS = T_OBS(indx).OBS_IDOBS;

            END IF;

        END LOOP;

    END AFTER STATEMENT;

END;
/

CodePudding user response：

A trigger can modify the values of the dml statement (insert/update/delete) only. An additional transaction on the same table within a trigger on that table is generally a bad idea.

But here is a workaround.

1. Create a table
2. Create a view on that table
3. Create an instead of trigger on the view
4. Insert into the view

Example:

CREATE TABLE MAIN_TABLE (id number);

Table MAIN_TABLE created.

CREATE OR REPLACE VIEW MAIN_VIEW AS
  SELECT id, 0 as new_rows FROM MAIN_TABLE;

View MAIN_VIEW created.

INSERT INTO main_view (id) values (100);

1 row inserted.

It is possible to insert into the view. Since there is a 1:1 relationship with the main_table this will insert a row in MAIN_TABLE

SELECT COUNT(*) FROM main_table;

  COUNT(*)
----------
         1

Now create the instead of trigger on MAIN_VIEW

CREATE OR REPLACE TRIGGER main_view_ioi
INSTEAD OF INSERT 
ON main_view
FOR EACH ROW
BEGIN
  FOR i IN 1 .. :NEW.NEW_ROWS LOOP
    INSERT INTO main_table (id) VALUES (i);
  END LOOP;
END;
/

Trigger MAIN_VIEW_IOI compiled

INSERT INTO main_view (id,new_rows) values (1,5);

1 row inserted.

Note that this will say "1 row inserted" because only 1 row was inserted in MAIN_VIEW. However, the instead of trigger created 5 rows in MAIN_TABLE.

SELECT * FROM main_table;

        ID
----------
       100
         1
         2
         3
         4
         5