Home » Developer & Programmer » Forms » Database table Trigger (Oracle 11g)
Database table Trigger [message #627365] Tue, 11 November 2014 06:41 Go to next message
metal_navin
Messages: 21
Registered: February 2013
Junior Member
Good day.
I have a table called NOTES, which maintains notes for customers.
You can have different note types, eg. NOTE1 and NOTE2.

My objective is when an insert is done into this table, another row is created simultaneously. This insert is done via Oracle Forms.
Eg. If I add a note of type NOTE1, then simultaneously, another entry is made in this table of say note type NOTE3.

I was looking at doing this via a database trigger, but unfortunately getting an error "ORA-04091: table name is mutating, trigger/function may not see it".

Can anyone please give a suggestion on how to handle this?
Re: Database table Trigger [message #627369 is a reply to message #627365] Tue, 11 November 2014 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Directly you can't.
Now tell us your actual business and then maybe we can give you a solution.

Re: Database table Trigger [message #627375 is a reply to message #627369] Tue, 11 November 2014 07:25 Go to previous messageGo to next message
metal_navin
Messages: 21
Registered: February 2013
Junior Member
We are creating a note via an oracle form.
now, this form would insert the new note row into the NOTES table.
However, I want to insert another row simultaneoulsy of a different NOTE TYPE.

Therefore, in total I would like to insert two rows via the form, but the user would only think they are inserting one row.

Please let me know if this isnt clear.
Re: Database table Trigger [message #627376 is a reply to message #627375] Tue, 11 November 2014 07:56 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
if this is a FORM, then do not use a database trigger, use a FORMS trigger. You didn't tell us "how" you save the record in the form, but it would be pretty academic to make a trigger to use the :block.column values in an INSERT statement based on a condition.
Re: Database table Trigger [message #627392 is a reply to message #627376] Tue, 11 November 2014 09:29 Go to previous messageGo to next message
xebec
Messages: 37
Registered: July 2014
Location: Miraflores
Member
Hi Man Smile
To undestand your point ;

You want to insert "notes" 1, note 2 , note 3
using oracle form , isn't it?

So... Do a oracle form, like maintance. Use Randonmize number as ID for each row (note)
then go go go finish! Very Happy
Re: Database table Trigger [message #627457 is a reply to message #627392] Wed, 12 November 2014 02:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's really vague, apart from the bit about populating ID, which is just wrong. You should use a sequence.
If you use random numbers then sooner or later you'll get a number that's been used before and then you'll get an ORA-00001 error.
Re: Database table Trigger [message #627537 is a reply to message #627457] Wed, 12 November 2014 11:37 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Quote:
I was looking at doing this via a database trigger, but unfortunately getting an error "ORA-04091: table name is mutating, trigger/function may not see it".

Which database trigger did you use?
Quote:
My objective is when an insert is done into this table, another row is created simultaneously. This insert is done via Oracle Forms.

So, why not just do this in Forms through the Post-Insert or Post-Update triggers?

Craig...


[EDITED by LF: fixed quote tags]

[Updated on: Thu, 13 November 2014 00:10] by Moderator

Report message to a moderator

Re: Database table Trigger [message #627539 is a reply to message #627537] Wed, 12 November 2014 11:41 Go to previous messageGo to next message
xebec
Messages: 37
Registered: July 2014
Location: Miraflores
Member
I INSIST
use:
in the pre-insert of the datablock
declare
	NSEQ NUMBER:=0;
begin
	SYNCHRONIZE;
	ctsp001(SUBSTR(:CIASUC,1,3),SUBSTR(:CIASUC,4,3),'RCL','1',NSEQ);
	:RGSNUMSOL := NSEQ;	
	:RGSAUDIFEC := SYSDATE;
	:RGSAUDIUSU := USER;
end;

Re: Database table Trigger [message #627542 is a reply to message #627537] Wed, 12 November 2014 11:48 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Quote:
I was looking at doing this via a database trigger, but unfortunately getting an error "ORA-04091: table name is mutating, trigger/function may not see it"

Which database trigger did you use?
Quote:
My objective is when an insert is done into this table, another row is created simultaneously. This insert is done via Oracle Forms.

So, why not just do this in Forms through the Post-Insert or Post-Update triggers?

Craig...

[Updated on: Wed, 12 November 2014 11:49]

Report message to a moderator

Re: Database table Trigger [message #627553 is a reply to message #627539] Wed, 12 November 2014 12:51 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
xebec wrote on Wed, 12 November 2014 12:41
I INSIST
use:
in the pre-insert of the datablock
declare
	NSEQ NUMBER:=0;
begin
	SYNCHRONIZE;
	ctsp001(SUBSTR(:CIASUC,1,3),SUBSTR(:CIASUC,4,3),'RCL','1',NSEQ);
	:RGSNUMSOL := NSEQ;	
	:RGSAUDIFEC := SYSDATE;
	:RGSAUDIUSU := USER;
end;


What question is this an answer to?

Previous Topic: visual attribute not working runtime
Next Topic: Oracle forms 10g and Windows vista
Goto Forum:
  


Current Time: Thu Mar 28 05:22:43 CDT 2024