AW: AW: Constraint violation in spite of trigger with sequence

From: A. Stiebing <stiebing_at_softcontract.de>
Date: Fri, 5 Feb 2010 12:10:49 +0100
Message-ID: <EB5EB41622384E60935E873E2BEAA14F_at_as>

[After having been short of time (sorry) I'll try to paste/answer your helpful hints as answer to this mail as this one contains most of the thread so far]

Regarding the creation of the sequence: it had been done only one time to ensure we are above the largest number.

The Trigger is always enabled and SQLLoader is not used, there are no before-insert triggers and no instead-of triggers, also no update trigger which does anything to NUM. Nothing else happens to :new.num in the trigger. Though there are several other triggers on the table it would be nearly impossible to post the whole chain of things which happen, there are packages called (which set status on other tables, where again triggers call packages which set status on other tables and so on) which, as far as i could follow them never touch the mentioned column again.

The columns CREATEDBY and CREATIONDATE are nullable, but set by the trigger if both are null (see below for complete trigger). Searching DBA_SOURCE nothing in the results looks like inserts/updates on the table at all, excpet the trigger below.

No views are used to update any data in our house, where the data to fill the table can come in through 3 (sorry to mention 5 before) different apps (from different developers in different languages and different knowledge depths :-/).

There is no on-update trigger at the moment - thanks for that hint - but I would state that can't be the cause as once I had the error as I could verify no one other was using the DB and the both applications I ran on it are verified (as I was told) to not update the table but only insert to it.

Did I already mention this problem comes up just from time to time? The event ist triggered some dozen times a day but the problem appears only once in a month or even less often, where the next try of an insert always succeeds.
My personal suspicion is that this only happens when there are heavy read actions at the same time on the table with the insert trigger, but I can't verify this really.

The full trigger is as following, thanks all for the help so far!
--

trigger TI_AUFT before insert on AUFT referencing old as old new as new for each row
begin

	if(:new.id is null) then
		select seq_AUFT.nextval into :new.id from dual;
	end if;

	select s_po_number.nextval into :new.num from dual;

	if(:new.creationdate is null and :new.createdby is null) then
		select nvl(:new.createdby, user), nvl(:new.lastwriter,
user), nvl(:new.creationdate, sysdate), nvl(:new.lastupdate, sysdate)
		into :new.createdby, :new.lastwriter, :new.creationdate,
:new.lastupdate from dual;
	end if;

end;
--

Regards A. Stiebing

---

---

Von: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] Im Auftrag von Tony van Lingen
Gesendet: Mittwoch, 3. Februar 2010 01:37 An: stiebing_at_softcontract.de
Cc: 'oracle-l'
Betreff: Re: AW: Constraint violation in spite of trigger with sequence

You would need an on-update trigger as well, to ensure that no process can update num after the sequence number has been inserted. If it does not exist, then some-one could update the field later with a value, e.g. gleaned from a select max().. done before the insert.

Cheers,
Tony

-----Ursprüngliche Nachricht-----

Von: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] Im Auftrag von Mark W. Farnham
Gesendet: Dienstag, 2. Februar 2010 18:44 An: stiebing_at_softcontract.de; 'Jay'; 'chet justice'; toon.koppelaars_at_rulegen.com
Cc: 'oracle-l'
Betreff: RE: AW: Constraint violation in spite of trigger with sequence

Does anything else happen to :new.num after it has been selected from the sequence in the bits of the trigger you have omitted?

Plus, did you answer the question from Toon yet? You should only be creating the sequence once, in a quiet period. (Or as a repair, I suppose, but not as part of the insert process each time).

---

---

Von: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] Im Auftrag von Jared Still
Gesendet: Dienstag, 2. Februar 2010 18:49 An: Adam Musch
Cc: stiebing_at_softcontract.de; Oracle-L Freelists Betreff: Re: Constraint violation in spite of trigger with sequence

On Tue, Feb 2, 2010 at 9:29 AM, Adam Musch <ahmusch_at_gmail.com> wrote:

The only ways I know of to avoid a trigger is:

1. Disable it.
2. Use SQL*Loader in direct mode, which will write blocks directly, bypassing triggers.

There are other ways to disable a trigger, which is why I asked to see the code. Back to productive work now.

Jared Still

 -----Ursprüngliche Nachricht-----
Von: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] Im Auftrag von Adam Musch
Gesendet: Dienstag, 2. Februar 2010 18:29 An: jkstill_at_gmail.com
Cc: stiebing_at_softcontract.de; Oracle-L Freelists Betreff: Re: Constraint violation in spite of trigger with sequence

The only ways I know of to avoid a trigger is:

1. Disable it.
2. Use SQL*Loader in direct mode, which will write blocks directly, bypassing triggers.

It could be that there are multiple before-insert triggers on the table, or an instead-of trigger of a view that shoots data into the table in question; in 9i, I know trigger invocation order is indeterminate, so some other trigger could be superseding the one listed.

On Tue, Feb 2, 2010 at 10:12 AM, Jared Still <jkstill_at_gmail.com> wrote:

> On Tue, Feb 2, 2010 at 2:09 AM, A. Stiebing <stiebing_at_softcontract.de>
> wrote:
>>
>>
>> In spite of we got an trigger with a sequence to update an unique id 
>> key on insert, we get constraint violations from time to time.
>> Where could the cause be for that?
>>
>
> 1) By what methods does data get into the table?
>     Custom app?  SQL Loader?  Custom Utilities?  Import?
>
> 2) Please post entire code for the trigger.
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog: http://jkstill.blogspot.com
> Home Page: http://jaredstill.com
>
>


________________________________

	Von: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] Im Auftrag von chet justice
	Gesendet: Dienstag, 2. Februar 2010 17:25
	An: stiebing_at_softcontract.de
	Cc: Jay; toon.koppelaars_at_rulegen.com; oracle-l
	Betreff: Re: AW: Constraint violation in spite of trigger with

sequence                  

        not if, and I'm assuming until I see the rest of the code, the CREATEDBY and CREATEDATE columns are both NOT NULL.

        Look through DBA_SOURCE to find all references to that table (assuming, yes I know, it's bad, that it's not an app from outside the database) and see if there is another entry point.                           

        On Tue, Feb 2, 2010 at 11:13 AM, A. Stiebing <stiebing_at_softcontract.de> wrote:         

                As I have to rely on what my colleague is stating I can't say 100% - but

                even if another program (potentially 5 different) would try to write the NUM

                column - shouldn't the trigger generate a new one?                 

                Regards                 

		-----Ursprüngliche Nachricht-----
		Von: Jay [mailto:jaykash_at_hotmail.com]
		Gesendet: Dienstag, 2. Februar 2010 16:41
		An: stiebing_at_softcontract.de; 'chet justice';
toon.koppelaars_at_rulegen.com
		Cc: 'oracle-l'
		Betreff: Re: AW: Constraint violation in spite of trigger

with sequence                 

                Any chances that something other than the trigger could also be populating

                this column? One-off inserts/ updates?                 

		Regards,
		Jay
		
		--------------------------------------------------
		From: "A. Stiebing" <stiebing_at_softcontract.de>
		Sent: Tuesday, February 02, 2010 8:52 AM
		To: "'chet justice'" <chet.justice_at_gmail.com>;
<toon.koppelaars_at_rulegen.com>
		Cc: "'oracle-l'" <oracle-l_at_freelists.org>
		Subject: AW: Constraint violation in spite of trigger with

sequence                 

                > There are other triggers, but none which have the string 'num' in them.

		>
		> I also tried a
		> --
		> select a.trigger_name, a.*
		> FROM all_triggers a,
		>  all_triggers b
		> WHERE a.trigger_name = b.trigger_name
		> AND a.table_owner    = b.owner
		> AND lower(b.table_name) LIKE 'foo'
		> or lower(b.description) like '%foo%';
		> --
		>
		> to check if any other trigger points to the table maybe -
where there
		> could be better ways to check these, I suppose.
		>
		> ...and this select(max)...was only to demonstrate that
this had been
		> done one time, yes
		>
		> Regards, A. Stiebing
		>
		>
		> ________________________________
		>
		> Von: chet justice [mailto:chet.justice_at_gmail.com]
		> Gesendet: Dienstag, 2. Februar 2010 14:45
		> An: toon.koppelaars_at_rulegen.com
		> Cc: stiebing; oracle-l
		> Betreff: Re: Constraint violation in spite of trigger with
sequence
		>
		>
		> 100% positive there is no other trigger on the table?
		>
		>
		>
		>
		> On Tue, Feb 2, 2010 at 8:39 AM, Toon Koppelaars
		> <toon.koppelaars_at_rulegen.com> wrote:
		>
		>
		> I assume you are showing us the 'select max(...' + the
'create
		> sequence' command because you have done that once.
		> Or are you doing this inside some process, *every time*?
		>
		> In the latter case. It could very well be that between the
'select
		> max' and the 'get nextval' some other session had been
adding a row to
		> that table, with a value that now also is generated by the
sequence.
		>
		>
		>
		> On Tue, Feb 2, 2010 at 11:09 AM, A. Stiebing
		> <stiebing_at_softcontract.de> wrote:
		>
		>
		> Hi all,
		>
		> hopefully someone can give me a hint in the correct
direction
		> regarding a constraint problem:
		>
		> In spite of we got an trigger with a sequence to update an
unique id
		> key on insert, we get constraint violations from time to
time.
		> Where could the cause be for that?
		>
		> Oracle 9
		> --
		> select max(num) from auft;
		> -- -> 52013
		> create sequence s_po_number NOMAXVALUE increment by
		> 1 start with 52014 cache
		> 20 noorder nocycle;
		>
		>
		> trigger TI_AUFT before insert on AUFT referencing old as
old new as
		> new for each row begin
		> -- (...)
		> select s_po_number.nextval into :new.num from dual;
		>
		> if(:new.creationdate is null and :new.createdby is
		> null) then
		> -- (...)
		> end;
		>
		> --
		>
		> Best regards
		>
		> A. Stiebing
		>
		>
		>
		>
		>
		>
		>
		>
		> --
		> Toon Koppelaars
		> RuleGen BV
		> Toon.Koppelaars_at_RuleGen.com
		> www.RuleGen.com
		> TheHelsinkiDeclaration.blogspot.com
		>
		> (co)Author: "Applied Mathematics for Database
Professionals"
		> www.RuleGen.com/pls/apex/f?p=14265:13
		>
		>
		>
		>
		>
		> --
		> http://www.freelists.org/webpage/oracle-l
		>
		>
		>
		
		

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 05 2010 - 05:10:49 CST