You doubt about trigger to update a field in the same table that triggers it

Question

You doubt about trigger to update a field in the same table that triggers it

Navigation

#1 by (0 votes)

1

I have created a trigger that triggers when I modify the value of a record of a table, in this case the number of components that I have in the company. That is, the trigger only allows me to modify this amount if the new value is less than 999. If not, an exception jumps. The fact is that when the value I enter in the field with a update is less than 999, I want the trigger to also change the value of the column amount (which is the product of cantidad*precio_venda ). The case is that whatever I do oracle returns me the error that the table is mutating and I do not know how I can make the trigger update the field when I update the amount. To be clear I copy below the code of the table I have created and my trigger , I think what I have to do is an autonomous transaction but I am not able to perform the correct praxis, I appreciate any help, thanks in advance :) .

Table detail that triggers the trigger

CREATE TABLE DETALLE  (
 COM_NUM             NUMBER (4),
 DETALLE_NUM          NUMBER (4),
 PROD_NUM            NUMBER (6) CONSTRAINT DETALLE_NN_PRODUCTO NOT NULL,
 PRECIO_VENDA          NUMBER (8,2),
 CANTIDAD           NUMBER (8),
 IMPORTE              NUMBER (8,2),
 CONSTRAINT DETALLE_FK_COMANDA FOREIGN KEY (COM_NUM) REFERENCES COMANDA (COM_NUM),
 CONSTRAINT DETALLE_FK_PRODUCTO FOREIGN KEY (PROD_NUM) REFERENCES PRODUCTO (PROD_NUM),
 CONSTRAINT DETALLE_PK PRIMARY KEY (COM_NUM,DETALLE_NUM),
 CONSTRAINT DETALLE_CK_DETALLE_NUM_POSITIU CHECK (DETALLE_NUM > 0),
 CONSTRAINT DETALLE_CK_PVP_NO_NEGATIU CHECK (PRECIO_VENDA >= 0));

Then my trigger

CREATE OR REPLACE TRIGGER verifica_unidades
AFTER INSERT OR UPDATE OF CANTIDAD ON DETALLE
for each row
BEGIN
IF :new.cantidad>999 then
RAISE_APPLICATION_ERROR(-20511,'¡Cantidad demasiado alta!');
end if;
end;

/

Greetings.

oracle trigger update

asked by Aridai Francés 17.11.2018 в 11:21

source

1 answer

Regular Expressions in Visual Basic.net Error trying to make custom queries to Mysql database

score 0 · Accepted Answer

You can not touch the same table that is being updated / modified within the same instruction.

To update a data within the same row that you are processing, what you usually do is write a trigger before insert or before update . In this you have access to the data before they are written in the row and you can manipulate them.

Something like:

CREATE OR REPLACE TRIGGER verifica_unidades
BEFORE INSERT OR UPDATE OF CANTIDAD ON DETALLE
for each row
begin
  :new.Total = :new.cantidad * :new.precio;
end;
/

I would also recommend doing the validation like the one you mentioned earlier in the same trigger before , because when raising the exception in the after trigger, the record is already written and this forces the base of data to undo that write before returning control. On the other hand, in the before, the load of server work is less.

To finish, I return to the topic of the mutant tables . It is something that you can not forget working with oracle and sometimes represents a limitation. You must take into account, for example, that if during a trigger in table A, you make an operation in table B, which in turn triggers a trigger that tries to update table A, there will be an error, since table A will be mutating at that time.

Generalizing, since a statement insert , update or delete is sent, the table on which it is launched and all those that are modified as a result of this, will be mutating until that instruction insert , update or delete is completed. That is, until the database returns control to the client, script or stored procedure that launched the instruction.