473,320 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

mutating table error w/trigger

i've found the solution threads on changing a column on insert. works
fine.

question:

- will one package serve for all such triggers, or does there need to
be a package defined to support each table?

my o'reilly book doesn't say, nor do the threads i found.

thanks,
robert
Jul 19 '05 #1
2 4678

"robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...
i've found the solution threads on changing a column on insert. works
fine.

question:

- will one package serve for all such triggers, or does there need to
be a package defined to support each table?

my o'reilly book doesn't say, nor do the threads i found.

thanks,
robert


I'd love to know what you're talking about. You seem to assume that I can
remember every thread there is on mutating tables or can be bothered to look
them all up. Ellaborate a little please.
Jul 19 '05 #2

"robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...
"Alan Mills" <Al********@xservices.pants.fujitsu.com> wrote in message news:<bg**********@news.icl.se>...
"robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...
i've found the solution threads on changing a column on insert. works
fine.

question:

- will one package serve for all such triggers, or does there need to
be a package defined to support each table?

my o'reilly book doesn't say, nor do the threads i found.

thanks,
robert


I'd love to know what you're talking about. You seem to assume that I can remember every thread there is on mutating tables or can be bothered to look them all up. Ellaborate a little please.

didn't mean to be obtuse. it seemed from reading the threads that the
"mutating table error" was well known to those, unlike myself, who had
been doing oracle for a while. the solutions in the various threads were
the same:

i) - create a package to hold a an array of rowids
ii) - create a before insert trigger to reset the array
iii) - create an after insert for each row trigger to build an array
of rowids of inserted rows
iv) - create an after insert trigger to parse the rows in the array,
doing the work.

since i don't yet know the internals of oracle, or even for sure, where
these triggers execute (in server process or client process), i'm

wondering whether one package will suffice for any number of "sets" of the triggers.
or whether it is necessary to create a package for each "set" of triggers.

this all arise because oracle, unlike DB2 for instance, won't allow a
trigger to modify a column in an inserted row. in my case, one column is
a "date" --> 12311998 which i want to turn into a sql date in a second
column. seemed simple enough, until the mutating table error.


OK thanks for the ellaboration.

From your last paragraph it looks like you are simply denomralising the date
value into another column in the same record.

If so then why not just have your BEFORE INSERT trigger say something like

:new.second_date_col := to_date(:new.original_date_col, 'MMDDYYYY');

other than that I think I might be personally inclined to have seperate
packages, for each table. It modularises the code better.
Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: M | last post by:
Hello, I have a very simple table, and want to create a trigger that updates the date column entry (with the current date), whenever a row gets modified. Is there a simple way of fixing this,...
4
by: M Mueller | last post by:
Hello - I'm using Oracle 8i and am running into the infamous mutating table error on an AFTER INSERT OR UPDATE FOR EACH ROW trigger. I have seen examples for getting around mutating table...
0
by: hankr | last post by:
I have only a developer's level knowledge of Oracle, though I have access to DBA tools. Searches through the docs have not been fruitful, so I thought I'd try here. I am getting an error on a...
2
by: Reshmi Jacob | last post by:
Hello, Can any one help me in creating a trigger to update system date into a table while inserting a record into that table. I tried it like this, it is showing error !!! The following error...
0
by: AYAN MUKHERJEE | last post by:
I have 2 tables. 1) emp_test, 2) newemp_test. I had created a trigger to insert automatic data in the table no. 2 , as soon as a row of information is inserted in table no. 1. The Trigger had been...
2
by: hemalatha natarajan | last post by:
hi, I just created a trigger to avoid inserting the same data into table . If same data is inserted then error msg should be thrown. I created a trigger but when it is executed during insertion it...
3
by: dmanojbaba | last post by:
i have a table with values like 1,2,3.... (primary key) if i delete a row eg.4, i need my trigger to update the values 5 to 4,6 to 5, 7 to 6,.. like that, after deleting 4. pls help me... my...
2
by: robert | last post by:
i've found the solution threads on changing a column on insert. works fine. question: - will one package serve for all such triggers, or does there need to be a package defined to support...
16
by: vamsioracle | last post by:
Can Someone help me how to avoid mutating error while using triggers. I work on oracle apps. I created a vacation rule such that responsibility is delegated to other person. These details are...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.