Home » SQL & PL/SQL » SQL & PL/SQL » Can an insert trigger add a row to the same table?
Can an insert trigger add a row to the same table? [message #39814] Tue, 13 August 2002 11:00 Go to next message
Graham
Messages: 9
Registered: March 2002
Junior Member
It would be really convenient if a trigger
after insert could, if type = 'A', insert
another a row of type 'B' into the same table.
If type is anything else do nothing.

Is this posible?
Thx

Report message to a moderator

Re: Can an insert trigger add a row to the same table? [message #39815 is a reply to message #39814] Tue, 13 August 2002 11:11 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You can certainly do this using a combination of an after row, before statement, and after statement trigger, and a package. This is all necessary to avoid a mutating table error which occurs if you try to insert directly from the after row trigger.

Here's a link that describes the approach:

http://osi.oracle.com/~tkyte/Mutate/index.html

Basically, you have an array in a package header that contains the information needed to create the additional row. This array is cleared in the before statement trigger, added to in the after row trigger (if the type is 'A'), and looped through in the after statement trigger.

Report message to a moderator

Re: Can an insert trigger add a row to the same table? [message #39823 is a reply to message #39814] Wed, 14 August 2002 04:33 Go to previous message
Rick
Messages: 49
Registered: March 2000
Member
Hmmm how do you ever get out of trigger I wonder???
I am not sure but perhaps take a look at solving mutating table problem. Look at following articles
on MetaLink Doc Id 74859.1, 37906.1

Report message to a moderator

Previous Topic: Urgent !!!! call pl/sql function which returns an Object Type having nested tables from Java
Next Topic: Re: Names with single quotation marks
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 16 01:36:50 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.