Re: Update trigger
- From: ddf <oratune@xxxxxxx>
- Date: Fri, 17 Oct 2008 07:50:52 -0700 (PDT)
On Oct 17, 9:30 am, "Shakespeare" <what...@xxxxxxxxx> wrote:
"Ed Prochak" <edproc...@xxxxxxxxx> schreef in berichtnews:c1a46f04-8b2b-4d34-b8fd-22de923c632e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Oct 17, 7:33 am, ddf <orat...@xxxxxxx> wrote:
Comments embedded.
On Oct 17, 5:37 am, phancey <d...@xxxxxxxxxxxx> wrote:
On 17 Oct, 11:25, sybrandb <sybra...@xxxxxxxxx> wrote:
On 17 okt, 12:00, phancey <d...@xxxxxxxxxxxx> wrote:
hi,
I need to write a history record for certain fields that change on a
table. I am not sure whether it is better to write a single AFTER
UPDATE OF column1,column2,column3 etc trigger that then uses if
statements to check each specific field whether it has changed OR to
create an AFTER UPDATE OF column1 ON mytable WHEN
nvl(new.column1,-1)
<> nvl(old.column1,-1) trigger for each column for example.
Is there a performance hit in having 7 AFTER UPDATE triggers
defined,
one for each column I am interested in rather than 1 trigger for all
7?
thanks for any help.
Phil
Triggers are parsed on invocation. Obviously 1 parse is less expensive
than 7 parses.
If you just use
IF updating('<column name>' then
you wouldn't embark on creating a performance nightmare, which looks
syntactically incorrect too.
--
Sybrand Bakker
Senior Oracle DBA- Hide quoted text -
- Show quoted text -
yes but it is only when the value changes that I should write the
history record. Rightly or wrongly the columns are all updated on a
record update even if we are only effectively changing one column
value (no point in discussing the merits of this as my hands are tied)
That's incorrect.
- not strictly true as there are a couple of instances where updates
are more targeted hence my use of a column list in the trigger
definition but certainly the update of a column does not guarantee
that it is changing.
But it does guarantee the value is changing unless the SET statement
uses the existing value.
IF updating('<column name>') then..... would write the history record
every time the column was updated NOT, as required, when it was
changed?
DAvid,
I think he is saying the target columns are set in the update
statement, but they may be set to their current value. If there is no
actual change, he does not want the log entry. Personally, I would
still want the log entry (otherwise the history is incomplete) and
would filter it out on any reports that did not need it.
<snip>
========================================================
Good point there: if you chose to, Designer writes the 'old' AND the 'new'
values to the journal tables, so needs them anyway. Comparing old and new
values can be done while reporting, taking away any perfomance issue from
the actual journaling process..
Shakespeare- Hide quoted text -
- Show quoted text -
In some implementations it is a requirement to capture all 'changes'
to a record (the Federal Aviation Administration here in the U.S.
requires such a trail). Filtering results for a report is much easier
than complicating a trigger with conditional logic to avoid
'unnecessary' history records.
David Fitzjarrell
.
- References:
- Update trigger
- From: phancey
- Re: Update trigger
- From: sybrandb
- Re: Update trigger
- From: phancey
- Re: Update trigger
- From: ddf
- Re: Update trigger
- From: Ed Prochak
- Re: Update trigger
- From: Shakespeare
- Update trigger
- Prev by Date: Re: Update trigger
- Next by Date: Re: Oracle 10g AWR Report - Analysis/Interpretation
- Previous by thread: Re: Update trigger
- Next by thread: FREE ONLINE TEST at http://www.yfrindia.com/resources/Tests : DATA STRUCTURE, OPERATING SYSTEM, COMPUTER NETWORK, JAVA, .NET, C, C++, MANY MORE
- Index(es):
Relevant Pages
|
