Database table Trigger [message #627365] |
Tue, 11 November 2014 06:41 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/289788c5c4d2bf860284ff8a61071853?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/6f539f43889b4b3e3fcb6c591073de8f?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/6f539f43889b4b3e3fcb6c591073de8f?s=64&d=mm&r=g) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
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?
|
|
|