Home » SQL & PL/SQL » SQL & PL/SQL » multiple table trigger or constraint
multiple table trigger or constraint [message #296148] Thu, 24 January 2008 20:22 Go to next message
stevekerver
Messages: 19
Registered: January 2008
Junior Member
...Assume I have two tables, which are related to each other, or related to each other in a series of relations- i.e., grandparent -> parent -> child.
The relationship is maintained via the standard auto-sequenced primary key method.

Both tables contain date values, and I want to ensure that a record could never be inserted or updated in the child table if it falls before the date value of the related parent.

So, for example, the parent table has one row of data with a primary key ID of 1, and a date value of 1/24/08.
In the child table, I should be able to enter anything after 1/24/08- but never before. (Like, trying to enter 1/23/08 should create an error).

Does oracle support cross-table constraints or triggers?

Here's what I've tried so far:

create table test(
test_id NUMBER,
test_day_start timestamp(6));

create table test2(
test2_id NUMBER,
test_id NUMBER,
test2_day_start timestamp(6));


insert into test values (1, sysdate);
insert into test values (2, sysdate+1);
insert into test values (3, sysdate+2);

commit;

insert into test2 values (1, 1, SYSDATE);
insert into test2 values (2, 1, SYSDATE+1.5);
insert into test2 values (3, 1, SYSDATE+1.75)
insert into test2 values (4, 1, SYSDATE+2)
insert into test2 values (5, 2, SYSDATE+4)
insert into test2 values (6, 2, SYSDATE+8)
insert into test2 values (7, 3, SYSDATE+11)

commit;

Commit complete.

...And now, I want the following insert statement to fail, because the record I'm trying to insert into the child table happens before the date of related record in the parent table...
--> insert into test2 values (8, 1, SYSDATE-1);
------------------------------
This didn't work:

create or replace trigger test2date_b4_test1_date after insert or update
on test2 for each row
declare
day_start test.TEST_DAY_START%TYPE;
begin
select t.test_day_start into day_start
from test t, test2 t2
where t.test_id = t2.test_id
and
t.test_id = :old.test_id;
if :new.test2_day_start < day_start THEN
RAISE_APPLICATION_ERROR(-20001, 'The Date you entered is before parent start day.');
end if;
end;
/

Likewise, this didn't even work either:

alter table test2 add constraint
test2_ck check (TEST2_DAY_START >= SYSDATE);

And, according to Burleson, the constraint approach wouldn't have ever worked anyway, because I would have wanted to reference the values in another table:
http://www.remote-dba.cc/t_garmany_easysql_check_constraint_triggers.htm

...ANY IDEAS?
Re: multiple table trigger or constraint [message #296150 is a reply to message #296148] Thu, 24 January 2008 21:08 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> ...ANY IDEAS?
In fact, "didn't work" is not very useful error description, Oracle has much better one (I would bet, mutating error in this case).
The problem is, you query TEST2 in the trigger and do not just use :NEW.TEST_ID directly, like
SELECT t.test_day_start INTO day_start
FROM test t
WHERE t.test_id = :new.test_id;
Also using :OLD values in BEFORE INSERT trigger is not a good idea as they are filled with NULL.
Re: multiple table trigger or constraint [message #296160 is a reply to message #296148] Thu, 24 January 2008 22:16 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK first things first:

1) you need to format posts. flyboy formatted his post for us, learn from that.

2) your question is a common one, glad to see you are exploring ideas. do some reading on mutating and constraining table errors.

3) in case you don't see it, flyboy has provided the select statement you should have written instead of the one you did. The sql statement you provided did unnecessary reading of the table that owns the trigger that is firing which is one way to get a mutating table error.

4) this solution won't really work in many situations becuase even if you use the flyboy solution, real tables have primary and foreign key relationships. If you have a foreign key between test2 and test, then once you use flyboy's code, you will get past the mutating table error but may be confronted with the constraining table error. Hence the reason why before you go any further, you need to do some more reading about mutating and constraing table errors; so you understand what you are up against.

5) post working code. some of your inserts are missing semi-colons (unless I cut/past badly). Not a big issue, but it does not lend support to your cause to post non-working examples.

** don't be mad about these errors; these errors are Oracle protecting your data in ambiguous update scenarios. other databases don't do this and your data gets all messed up, so these errors, although they can from time to time make it harder to do stuff, are doing a good job of data quality for us.

So, now to your question. There is no easy way around this need. There are several possible solutions, but you should understand this first:

NONE OF THESE SOLUTIONS IS WITHOUT ITS DETRACTORS, SO DON'T JUMP INTO ONE. TALK WITH YOUR DBA AND ARCHITECT FIRST!

You should consult with your DBA and Architect before exploring them, and you should be doing a month of research before implementing any of them. So if you are looking for an quick solution, stop reading cause their isn't one.

Some possible solutions are:

1) use an abstraction layer on top of your tables. In oracle database terms this means, views and instead of triggers. This works pretty well, but it is not a quick fix approach, it should be part of an overall philosphy of your system design and I bet your system ain't got no philosophy does it? Do you have an architect or DBA who can guide you?

2) use autonomous transactions in your triggers. This is as bad an idea as it is easy to do, but some people are willing to live with bad if it is easy; don't be one of them. Read my OraFaq paper about autonomous transactions, then don't do this. I had to present this idea here to you so that should you here about it in your travels, you will have read my caution first.

3) disable foreign keys between child and parent tables, and/or up/down an entire integrity chain, in order to avoid constraining table errors. Another bad idea. What good is using a relational database like oracle if you are going to turn off its most significant features.

4) use a materialized view that has a constraint check, using fast refresh on commit. This is actually kind of cool, but it is delayed enforcement of your rule so consider what that means, and consider if the performance and space overheads justify what you are getting.

5) make your app do the work. Yes this flys against the idea of the database enforcing rules about the data, and I am a data guy at heart so I always look for ways to enforce data checks in the database. But I am also pragmatic. An application strategy in this case: is doable, is easy to understand and maintain, is relatively cheap, and has few if any hidden gotchas. Boy, I don't usually say to enforce data rules in the app...

So that you know I am not blowing smoke, I offer up a simple example of #1 (instead of triggers) and how you might do what you want using them. Again don't jump at this, this is just a learning tool to get you started. Research all the options I have suggested on line and get nice and overloaded in your brain. This kind of mental stress helpsr you brain grow and emphasizes the feeling of trouble you can get into when you try to do something for real, using methods you don't understand.

Instead Of Trigger example: here you go, modifying your starting code:

create table test_tab(
test_id NUMBER,
test_day_start date);

create or replace view test as select * from test_tab;

create or replace trigger ioi_test
instead of insert on test
for each row
begin
   insert into test_tab (
                           test_id
                         , test_day_start
                        )
      values
              (
                 :new.test_id
               , :new.test_day_start
              )
   ;
end;
/
show errors


create table test2_tab(
test2_id NUMBER,
test_id NUMBER,
test2_day_start date);

create or replace view test2 as select * from test2_tab;

create or replace trigger io_test2
instead of insert on test2
for each row
begin
   insert into test2_tab (
                            test2_id
                          , test_id
                          , test2_day_start
                         )
      select
                 :new.test2_id
               , :new.test_id
               , :new.test2_day_start
      from dual
      where not exists
         (
            select null
            from test
            where test.test_id = :new.test_id
            and test.test_day_start >= :new.test2_day_start
         )
   ;
   if sql%rowcount = 0 then 
raise_application_error(-20999,
'trigger:io_test2:test2_day_start is on or before parent''s day_start');
 end if;
end;
/
show errors


insert into test values (1, sysdate);
insert into test values (2, sysdate+1);
insert into test values (3, sysdate+2);

commit;

select * from test;

insert into test2 values (1, 1, SYSDATE);
insert into test2 values (2, 1, SYSDATE+1.5);
insert into test2 values (3, 1, SYSDATE+1.75);
insert into test2 values (4, 1, SYSDATE+2);
insert into test2 values (5, 2, SYSDATE+4);
insert into test2 values (6, 2, SYSDATE+8);
insert into test2 values (7, 3, SYSDATE+11);

commit;

select * from test2;

--------------- the insert that you want to fail, will fail

insert into test2 values (8, 1, SYSDATE-1);


some points of interest:

1) My view names are your original table names. why did I do this? A question of design philosphy (do you know what design philosphy is about?).

2) there are no primary or foreign key constraints defined in your example. You should use an example that includes these as these will be part of your real database won't they?

3) my error message is a little lacking, I should have included enough of the data that caused the error so that people debugging the problem will have at least some shot at figuring out what actually happened.

4) if you have problems with any of these ideas, or you do something dumb like go to production with one of them without first doing the reseach and testing you should have done, and without involving someone else like your DBA or ARCHITECT so that you don't have to take the heat alone, and you hose yourself and need help because of it, don't call me, I'll call you.

Run the code, see the output, be in awe of it, then close your jaw and get busy talking with your DBA and ARCHITECT about the problem you are addressing and its possible solutions. Get GOOGLING and lookup the stuff I mentioned. I leave it to you to learn, write, test, and evaluate the other possible solutions I mentioned. This is actually a great thing for you to be faced with. If you do the research and work I have suggested, then no matter which way you go a month (two, or three) from now, you will have taken a big step forward as an Oracle Developer.

My cat Sparky says it is time for bed, so Have Fun, and Good luck, Kevin

[Updated on: Fri, 25 January 2008 00:38] by Moderator

Report message to a moderator

Re: multiple table trigger or constraint [message #296181 is a reply to message #296148] Fri, 25 January 2008 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have a complex logic between tables the best way is to allow update/insert/delete ONLY via procedure that implements this logic and forbid any direct insert/update/delete.

If you want to do it through trigger, you have to previously lock the tables or work alone.

By the way, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals) and error message.


Regards
Michel

[Updated on: Fri, 25 January 2008 00:45]

Report message to a moderator

Re: multiple table trigger or constraint [message #296271 is a reply to message #296148] Fri, 25 January 2008 08:13 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Once again Michel has the best answer of all.

Kevin
Previous Topic: Date Function
Next Topic: FIND table FROM all_tab_cols WHERE column value = something
Goto Forum:
  


Current Time: Tue Dec 06 12:31:26 CST 2016

Total time taken to generate the page: 0.13728 seconds