| Database table Trigger [message #627365] |
Tue, 11 November 2014 06:41  |
 |
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 #627376 is a reply to message #627375] |
Tue, 11 November 2014 07:56  |
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 #627457 is a reply to message #627392] |
Wed, 12 November 2014 02:47 |
cookiemonster
Messages: 13963
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 |
 |
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 #627542 is a reply to message #627537] |
Wed, 12 November 2014 11:48 |
|
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 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
xebec wrote on Wed, 12 November 2014 12:41I 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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
