473,698 Members | 2,403 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Implementing complicated constraints

Hi, I'm having trouble with implementing some constraints on the database
level. An example:

--Table A(AID, BID ref. B.BID, ATXT)
--Table B(BID, CID ref. C.CID)
--Table C(CID)

upon insertion into or updating in A I would like to force that ATXT is
unique with respect to CID, i.e.

SELECT COUNT(*) FROM A,B,C
WHERE A.BID = B.BID
AND B.CID = CID
AND CID = fn_get_cid_for_ bid(:new.BID)
AND A.ATXT = :new.ATXT
AND A.AID <> :new.AID

should be 0.

This I cannot force with a check constraint since it is not allowed to
contain subqueries.
If I try to write a trigger that checks the above condition and raises an
application error, then I always stumble upon the "table is mutating,
trigger/function may not see it" -type error, since it involves a select on
the table that is being changed at the time.

However, it would be better to implement such a constraint on the database
level, rather than scatter checks throughout the application.

Is there a standard way solve this type of problem?

Jul 19 '05
15 4541
Noons wrote:
Holger Baer <ho*********@sc ience-computing.de> wrote in message news:<cj******* ***@news.BelWue .DE>...

However I'm sure instead of trigger where available in 8.0, whereas

I don't think so.


This is an excerpt from the docs on my prehistoric 8.0.6 CD for Windows:
<quote>
-- INSTEAD OF Triggers
Use INSTEAD OF triggers to perform DELETE, UPDATE, or INSERT operations on views, which are not
inherently modifiable. "The View Query" for a list of constructs that prevent inserts, updates, or
deletes on a view. In the following example, customer data is stored in two tables. The object view
ALL_CUSTOMERS is created as a UNION of the two tables, CUSTOMERS_SJ and CUSTOMERS_PA. An INSTEAD OF
trigger is used to insert values:

CREATE TABLE customers_sj
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2) );

CREATE TABLE customers_pa
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2) );

CREATE TYPE customer_t AS OBJECT
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2),
location VARCHAR2(20) );

CREATE VIEW all_customers (cust)
AS SELECT customer_t (cust, address, credit, 'SAN_JOSE')
FROM customers_sj
UNION ALL
SELECT customer_t(cust , address, credit, 'PALO_ALTO')
FROM customers_pa;

CREATE TRIGGER instrig INSTEAD OF INSERT ON all_customers
FOR EACH ROW
BEGIN
IF (:new.location = 'SAN_JOSE') THEN
INSERT INTO customers_sj
VALUES (:new.cust, :new.address, :new.credit);
ELSE
INSERT INTO customers_pa
VALUES (:new.cust, :new.address, :new.credit);
END IF;
END;

</quote>

And I remember them being available for sure because then I was a developer
at a small company and we weren't allowed to use any enterprise feature because
it would make the initial licence cost so much higher for our prospective
customers.

Plus, after I moved to my current employer, I started to support an application
that made heavily use of views with instead of triggers. They used 8.0.6 too.
And if I didn't force them, they would still ...
Cheers,

Holger
Jul 19 '05 #11
Holger Baer <ho*********@sc ience-computing.de> wrote in message news:<cj******* ***@news.BelWue .DE>...
This is an excerpt from the docs on my prehistoric 8.0.6 CD for Windows:
Yeah, I believe you. Did a little "digging" myself and found
out. I'll have to understand why TK is going for FBI rather
than instead of. Doesn't make sense.
Plus, after I moved to my current employer, I started to support an application
that made heavily use of views with instead of triggers. They used 8.0.6 too.
And if I didn't force them, they would still ...


:) Takes all kinds, doesn't it?
My last 8.0.6 was Feb 03. After I sent them
a memo in early 01 explaining the dangers of staying
with old releases, they finally decided to upgrade in
Feb 03. Peoplesoft site. What else...
Jul 19 '05 #12
Noons wrote:
Holger Baer <ho*********@sc ience-computing.de> wrote in message news:<cj******* ***@news.BelWue .DE>...

This is an excerpt from the docs on my prehistoric 8.0.6 CD for Windows:

Yeah, I believe you. Did a little "digging" myself and found
out. I'll have to understand why TK is going for FBI rather
than instead of. Doesn't make sense.


I believe in his book (which I haven't got at hand right now) Tom explains why,
maybe I get the chance for a little digging myself. My rather clumsy recollection
is that this way you've declarative referential integrity and don't have to dig
through the code.

Plus, after I moved to my current employer, I started to support an application
that made heavily use of views with instead of triggers. They used 8.0.6 too.
And if I didn't force them, they would still ...

:) Takes all kinds, doesn't it?
My last 8.0.6 was Feb 03. After I sent them
a memo in early 01 explaining the dangers of staying
with old releases, they finally decided to upgrade in
Feb 03. Peoplesoft site. What else...


Take any customized software or worse, one of the famous home grown applications
the customer wrote himself (and the original developers as well as the documentation
are lost in the mist of time)... Oh, and in Feb 03 I could convince another customer
at least to move from 7.3.4 to 8.1.6 which got finally disposed this summer.

Did I already tell that today(!) I was called because at another site their hp-ux 10.20/ Oracle
8.0.4 server died? Lucky ol' us that we didn't support that one. Backups exist. But no
Hardware to fix or replace the old one....
Cheers,

Holger
Jul 19 '05 #13
Holger Baer <ho*********@sc ience-computing.de> wrote in message news:<cj******* ***@news.BelWue .DE>...
Did I already tell that today(!) I was called because at another site their hp-ux 10.20/ Oracle
8.0.4 server died? Lucky ol' us that we didn't support that one. Backups exist. But no
Hardware to fix or replace the old one....


Nnow the feeling only too well. Had a customer for a few years on
7.3.4 NT4. They never bothered to upgrade even though their PS HR
app was carking everywhere with 16-bit code. Then they kicked us out:
apparently we were "too expensive" even though we were charging one
tenth what the competition does.

Then they had the nerve to call us last year to "come and
fix it" when the server (a Pentium 1!) finally went up in flames.
And they sort of expected us to do this under some presumed
"warranty"! Of course. Look, there goes another pig...
Jul 19 '05 #14
Noons wrote:
Holger Baer <ho*********@sc ience-computing.de> wrote in message news:<cj******* ***@news.BelWue .DE>...

This is an excerpt from the docs on my prehistoric 8.0.6 CD for Windows:

Yeah, I believe you. Did a little "digging" myself and found
out. I'll have to understand why TK is going for FBI rather
than instead of. Doesn't make sense.


I went through chapter 7 of Tom Kyte's book again. It makes perfect sense in
the cases he described, because with the trigger you either lock the
tables in question, or you end up with constraint violations:

Session A:
insert into view //trigger fires check constraints ok
Session B:
insert into view // trigger fires check
// constraints ok

Session A:
commit; // trigger does not reevalute constraints
Session B:
commit; // now we violated our constraint

I don't have the time at hand to check the proposed solution if it
behaves any different, but at least this is something to watch out.
And it's the reason why Tom chooses FBI over trigger.
Cheers,

Holger
Jul 19 '05 #15
Holger Baer <ho*********@sc ience-computing.de> wrote in message news:<cj******* ***@news.BelWue .DE>...

I went through chapter 7 of Tom Kyte's book again. It makes perfect sense in


Thanks, going to re-read that one.
Jul 19 '05 #16

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

Similar topics

2
10725
by: Paul | last post by:
Hi: I am not sure whether somebody can sort it out for me. I am doing data transfer from one oracle database to another. Both of them has the same structure (like same tables etc).the only thing i need to do is data transfering. i designed a vb program to do this, What i am worrying is when I run this program it might fire some constrants. Anybody can give me some
2
2699
by: Dmitri Zhukov | last post by:
Hello everyone, I am implementing software which has a medium sized number of text strings (max 100K) which are represented in a listbox. I want user to be able to search the string by typing the first letters of the record and showing coinciding record if any (similar to quickserach functionality of Norton/Total commander). So i guess I need some kind of hasing algorithm to be able to find string by its prefix. Any recommendations?
4
2028
by: Dmitri | last post by:
I just looked at a coworker's stored procedure and this person is dropping 4 Foreign key constraints and then re-adding them after processing the required logic (updating rows in the 4 tables in question). Is there *ANY* good reason to do this? What are the performance implications of doing this - negative or otherwise? I was furious when I found this because every once in a while I would notice that the constraints would be in...
2
5757
by: DW | last post by:
Greetings: I have to do a one-off forceful change of some data in a database. I need to disable some FK constraints, make the data change, and then re-enable the constraints. My process will be: ALTER TABLE TABLE1 NOCHECK CONSTRAINT FK_TABLE1_TABLE2 UPDATE TABLE1 SET COLUMN1=2 WHERE COLUMN2='Problem row'
10
6966
by: serge | last post by:
I am doing a little research on Google about this topic and I ran into this thread: http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/63cce060ff152dcc/1dc13d4ee6758966?lnk=st&q=difference+constraint+sql+defaults&rnum=14#1dc13d4ee6758966 I read SQL Server MVP Louis Davidson's post saying: "Actually they are more likely to drop the concept of bound defaults. Constraints are the standard way to do...
0
1430
by: BobTheDatabaseBoy | last post by:
i've Googled some this morning, but to my surprise, i don't find any offering (for fee or open source), which would integrate with, say Jakarta Struts, to provide the UI edits from cataloged constraints/triggers. there have been some threads, even recently, dealing with with the subject of where the "true" repository of data integrity should be. the database is the answer of course, but there needs to be some (easy) way to manage the...
2
3392
by: Ole Nielsby | last post by:
First, bear with my xpost. This goes to comp.lang.c++ comp.lang.functional with follow-up to comp.lang.c++ - I want to discuss an aspect of using C++ to implement a functional language, and I'd like the attention of fp as well as C++ gurus if available. The language I'm implementing - PILS - is dynamically
3
1631
by: Daniel Kraft | last post by:
Hi, I usually program in C++, but for a special project I do have to code in C (because it may be ported to embedded-like platforms where no C++ compiler exists). I do want to realize some kind of polymorphic behaviour, like this: I have some abstract base "class" Base, that is, a struct containing a vtable of function-pointers and possibly some common fields (but at the
15
314
by: Agoston Bejo | last post by:
Hi, I'm having trouble with implementing some constraints on the database level. An example: --Table A(AID, BID ref. B.BID, ATXT) --Table B(BID, CID ref. C.CID) --Table C(CID) upon insertion into or updating in A I would like to force that ATXT is unique with respect to CID, i.e.
0
8609
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9169
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8899
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8871
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7738
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6528
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4371
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3052
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2007
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.