Is it possible to call database trigger from Java code?

Question

I searched a bit and I found some info on how to call java code from database trigger but none about the opposite. Isn't it possible to call a trigger from a java method? After inserting to one table (Table1), I need to create several rows to another table (Table2) from a select on the first one. I built the trigger but if I make it to execute after insert on the first table I get an error:

ORA-04091: table Table1 is mutating, trigger/function may not see it ORA-06512:....

I am working on an ADF application, and as Table1-Table2 have a master detail relationship, maybe it doesn't allow inserting rows this way. That is why I thought that calling the trigger through a button may solve my problem. Any idea?

Trigger:

CREATE OR REPLACE TRIGGER Table2
AFTER INSERT ON Table1 REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW 
BEGIN
insert into Table2
(
Select col1,col2... from Table1
);
END;

Przemyslaw Kruglej · Accepted Answer · 2013-11-08 09:00:43Z

3

Triggers can't be called directly, they are only executed before/after rows are inserted/updated/deleted.

The problem is that in your trigger you are trying to access the table for which you have created your trigger, and that results in the mutating trigger error, because the trigger won't see the changes in the table.

Can you show use the code of your trigger? Why do you have to access that table? Maybe you could just use a stored procedure to achieve what you need and call it?

Edit

If you want to access the values from inserted row, you should just use the :new pseudorecord:

CREATE OR REPLACE TRIGGER Table2
AFTER INSERT ON Table1 REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW 
BEGIN
  insert into Table2 values (:new.some_col, :new.some_col2, <other columns>);
END;

edited Nov 8, 2013 at 9:00

answered Nov 8, 2013 at 8:51

Przemyslaw Kruglej

8,1412 gold badges30 silver badges41 bronze badges

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

1 Comment

Noah Martin Over a year ago

Yes thank you, this actually does the job :) And thanks for explaining that triggers can't be called directly.

benek · Accepted Answer · 2013-11-08 09:00:37Z

1

For calling your treatment you can create a PL/SQL Procedure/function and copy/paste (modulo some modifications) your trigger code in this procedure.

answered Nov 8, 2013 at 9:00

benek

2,1682 gold badges28 silver badges39 bronze badges

2 Comments

Noah Martin Over a year ago

Can you help me with a procedure sample, I am a total beginer in PL/SQL :| Thanks

benek Over a year ago

It is not complicate at all espacially if you already have a trigger. you have 3 blocks : DECLARE/BEGIN/EXCEPTION. Put your insert statement in the begin section and run it with todd or equivalent. Google "pl/sql procedure example".

daark · Accepted Answer · 2013-11-08 08:46:59Z

0

From what i understand, no insert should complete until the trigger has been completed perhaps there is an issue with the insert statement?

answered Nov 8, 2013 at 8:46

daark

3263 silver badges10 bronze badges

1 Comment

Noah Martin Over a year ago

thanks, but I made trigger AFTER INSERT... Shouldn't it be executed after insert operation?

Collectives™ on Stack Overflow

Is it possible to call database trigger from Java code?

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related