Hi group,

I have 3 tables: tblOne, tblTwo and tblThree.

All of my items are stored in tblOne. However, based on the item-type
information must be added in either tblTwo or tblThree (not both).

Is there some way to force this behaviour? For example, when I try to add
an item with type 'Two' information has to be written to tblTwo?

Right now, my application enforces this logic but I would like to put
that responsibility in the database.

TIA,

SomeDude

SomeDude wrote:
> Hi group,
>
> I have 3 tables: tblOne, tblTwo and tblThree.
>
> All of my items are stored in tblOne. However, based on the item-type
> information must be added in either tblTwo or tblThree (not both).
>
> Is there some way to force this behaviour? For example, when I try to add
> an item with type 'Two' information has to be written to tblTwo?
>
> Right now, my application enforces this logic but I would like to put
> that responsibility in the database.
>
> TIA,
>
> SomeDude
>

Triggers would normally be used to handle this...

Example modified from
http://dev.mysql.com/doc/refman/5.0/...e-trigger.html

Given:
insert into t1 (c1, c2, c3, c4, c5) values (1,2,3,4,5);

DELIMITER |
CREATE TRIGGER testref AFTER INSERT ON t1
FOR EACH ROW BEGIN
if new.c2 = 2
then
INSERT INTO t2 (c3) values (NEW.c1);
end if;
if new.c2 = 3
then
INSERT INTO t3 set c3 = NEW.c1;
end if;
END;
|
DELIMITER ;

Yeah, the data here makes no sense- but you get the idea.