CODEWITHSUNDEEP
oracle-databaseoracle11gtriggersinsert
hermione
hermione

Reputation: 35

Oracle trigger insert to other table then modify the original table

I have theses two tables:

TABLE ASSET_ENTRY_NOTE (
  ID                  NUMBER         NOT NULL, --PK
  ASSETMDL_ID         NUMBER         NOT NULL, --FK
  DEPT_ID             NUMBER         NOT NULL, --FK
  LOCATION            NVARCHAR2(100) NOT NULL,
  ASSET_ID            NUMBER,                  --FK TO ASSETS
  ACCOUNT_ID          NUMBER         NOT NULL, --FK
  TOTAL_DPRC_DURATION FLOAT(126)     NOT NULL,
  TOTAL_PROD_HRS      FLOAT(126),
  AMORTIZATION_PRCNTG FLOAT(126),
  ACQUIRE_DATE        DATE           NOT NULL,
  DESCRIPTION         NVARCHAR2(200) NOT NULL,
  APPRFLAG            NUMBER         DEFAULT 0 NOT NULL,
  WRK_HRS             FLOAT(126),

)

TABLE ASSETS (
 ID                   NUMBER         NOT NULL, --PK
 ASSETMDL_ID          NUMBER         NOT NULL, --FK
 DEPT_ID              NUMBER         NOT NULL,
 LOCATION             NVARCHAR2(100) NOT NULL, --FK
 ACCOUNT_ID           NUMBER         NOT NULL,
 ACQUIRE_DATE         DATE           NOT NULL,
 TOTAL_DPRC_DURATION  FLOAT(126),
 BALANCE_CLOSING_DATE DATE,
 SELL_VAL             FLOAT(126),
 RPLCMNT_DISCOUNT     FLOAT(126),
 DESCRIPTION          NVARCHAR2(200) NOT NULL,
)

Note that there's a one to one relationship between the two tables (i.e. ASSET_ENTRY_NOTE.ASSET_ID is Unique. When the ASSETS_ENTRY_NOTE.APPRFLAG is updated to 1 I have this trigger that:

  1. gets a new primary key sequence for the ASSETS table.
  2. insert data from ASSETS_ENTRY_NOTE to ASSETS.
  3. updates the column ASSETS_ENTRY_NOTE.ASSET_ID to the same value as the primary key value on the sequence.

This is the latest try for my trigger:

CREATE OR REPLACE TRIGGER ENTRYNT_ASSET_TRIG
after UPDATE OF APPRFLAG ON ASSET_ENTRY_NOTE
  for each row
  when (new.apprflag = 1)
declare
   v_asset_id number;
BEGIN
 SELECT assets_PK_SEQ.NEXTVAL INTO v_asset_id   
  FROM DUAL d;

 insert into assets (ID,
                                    assets.assetmdl_id,
                                    assets.dept_id,
                                    assets.location,
                                    assets.account_id,
                                    assets.acquire_date,
                                    assets.total_dprc_duration,
                                    assets.description
                                    )

                           values (v_asset_id,
                                   assetmdl_id,
                                   dept_id,
                                   location,
                                   account_id,
                                   acquire_date,
                                   total_dprc_duration,
                                   description
                                   );

 update ASSET_ENTRY_NOTE set asset_id = v_asset_id where ;
END;

The thing is, I know that ASSET_ENTRY_NOTE is a mutating table and the last UPDATE statement is not allowed here, But nothing else is working for me. What I've already tried:

  1. creating a statement-level trigger to update one value only.
  2. using before instead of after but that's incorrect because I need the values just to insert into the ASSETS.
  3. using a cursor to go through each value changed but I had exact fetch error.
  4. creating a procedure that handles inserting and updating.

Any help would be appreciated.

Upvotes: 0

Views: 924

Answers (3)

hermione
hermione

Reputation: 35

I fixed it and it worked:

  1. changed to before.
  2. edited the update statement to an assignment of new so that the last line would become :new.asset_id := v_asset_id ;

Upvotes: 0

user330315
user330315

Reputation:

The design seems quite strange to me, but to answer the question about the trigger:

To change the asset_entry_note row in the trigger, you need a before update trigger. In there you can just assign the value to the asset_id column.

Your insert statement is also wrong. You can table-qualify column names in the column list of an insert statement. And the values clause needs to use the values from the inserted row. You are referencing the target table's columns which is not allowed).

You also don't need a select statement to obtain the sequence value.

Putting all that together, your trigger should look something like this:

CREATE OR REPLACE TRIGGER ENTRYNT_ASSET_TRIG
BEFORE UPDATE OF APPRFLAG ON ASSET_ENTRY_NOTE
  for each row
  when (new.apprflag = 1)
declare
   v_asset_id number;
BEGIN
  v_asset_id := assets_PK_SEQ.NEXTVAL;

  insert into assets 
    (ID,
     assetmdl_id,
     dept_id,
     location,
     account_id,
     acquire_date,
     total_dprc_duration,
     description)
   values 
     (v_asset_id,
      new.assetmdl_id, -- reference the inserted row here!
      new.dept_id,
      new.location,
      new.account_id,
      new.acquire_date,
      new.total_dprc_duration,
      new.description);

  new.asset_id := v_asset_id;
END;
/

Upvotes: 1

user6115178
user6115178

Reputation:

You have to change the design of the application to have only one table with sign to indicate the membership of a particular entity.

Another way is to create 'after statement' trigger to update all affected rows in ASSET_ENTRY_NOTE with proper values. These rows is to be collected in, for example, package collection in row-level trigger.

Upvotes: 0

Related Questions