Hi Igor, thanks for your help. Is the 'WHEN' clause used very often when creating triggers or do you think I could get away with leaving it out?
I am unsure as what to put in the expression part of the WHEN clause in order to test it thoroughly. Also does anyone have a test database I could use to test out my triggers thoroughly as the one I created doesn't really lend itself to logical updates of other tables. Here is the one I created for testing purposes :- CREATE TABLE owner (ownerID number(2), surname varchar2(10), forename varchar2(10), address varchar2(20), primary key(ownerID)); CREATE TABLE vet (vetID number(3), name varchar2(10), primary key (vetID)); CREATE TABLE pettype (pettypeID number(2), pettype varchar2(10), legcount number(2), primary key(pettypeID)); CREATE TABLE pet (petID number(2), ownerID number(2), pettypeID number(2), petname varchar2(10), primary key (petID)); CREATE TABLE food (foodID number(2), food varchar2(20), primary key(foodID)); CREATE TABLE diet (pettypeID number(2), foodID number(2), primary key (pettypeID,foodID)); CREATE TABLE visit (visitID number(5), petID number(2), vetID number(3), treatment varchar2(20),cost number(4,2), primary key (visitID)); INSERT INTO owner values (1,'Abbott','Andy','1 the Avenue'); NSERT INTO owner values (2,'Abbott','Zak','2 the Ambles'); INSERT INTO owner values (3,'Bailey','Bill','3 the Boulavard'); INSERT INTO owner values (4,'Charles','Cynthia','4 the Court'); INSERT INTO owner values (5,'Duck','Howard','5 the Downs'); INSERT INTO owner values (6,'Evans','Ethel','6 the Elms'); INSERT INTO owner values(7,'Flintstone','Fred','7 the Fells'); INSERT INTO vet values (100,'Williams'); INSERT INTO vet values (200,'Xavier'); INSERT INTO vet values (300,'Young'); INSERT INTO vet values (400,'Zorro'); INSERT INTO pettype values (1,'Dog',4); INSERT INTO pettype values (2,'Cat',4); INSERT INTO pettype values (3,'Mouse',4); INSERT INTO pettype values (4,'Fish',0); INSERT INTO pettype values (5,'Ant',6); INSERT INTO pettype values (6,'Spider',8); INSERT INTO pettype values (7,'Starfish',5); INSERT INTO pettype values (8,'Snake',0); INSERT INTO pettype values (9,'Duck',2); INSERT INTO food values (1,'dog food'); INSERT INTO food values (2,'cat food'); INSERT INTO food values (3,'biscuits'); INSERT INTO food values (4,'cheese'); INSERT INTO food values (5,'worms'); INSERT INTO food values (6,'flies'); INSERT INTO food values (7,'amoeba'); INSERT INTO food values (8,'grass'); INSERT INTO food values (9,'pond weed'); INSERT INTO food values (10,'wood'); INSERT INTO food values (11,'nectar'); INSERT INTO food values (12,'chocolate drops') INSERT INTO diet values (1,3); INSERT INTO diet values (1,12); INSERT INTO diet values (2,2); INSERT INTO diet values (2,12); INSERT INTO diet values (3,4); INSERT INTO diet values (4,5); INSERT INTO diet values (4,6); INSERT INTO diet values (4,7); INSERT INTO diet values (4,9); INSERT INTO diet values (5,10); INSERT INTO diet values (5,11); INSERT INTO diet values (6,6); INSERT INTO diet values (7,7); INSERT INTO diet values (8,5); INSERT INTO diet values (8,6); INSERT INTO diet values (9,8); INSERT INTO diet values (9,9); INSERT INTO diet values (1,1); INSERT INTO pet values (1,1,1,'Fido'); INSERT INTO pet values (2,1,1,'Gnasher'); INSERT INTO pet values (3,1,1,'Wolfie'); INSERT INTO pet values (4,2,2,'Fluff'); INSERT INTO pet values (5,2,2,'Felix'); INSERT INTO pet values (6,2,1,'Poochie'); INSERT INTO pet values (7,3,4,'Nemo'); INSERT INTO pet values (8,3,4,'Wanda'); INSERT INTO pet values (9,3,4,'Fred'); INSERT INTO pet values (10,3,4,'Titus'); INSERT INTO pet values (11,3,8,'Hiss'); INSERT INTO pet values (12,3,8,'Sid'); INSERT INTO pet values (13,4,2,'Tom'); INSERT INTO pet values (14,4,2,'Garfield'); INSERT INTO pet values (15,4,3,'Jerry'); INSERT INTO pet values (16,4,3,'Mickey'); INSERT INTO pet values (17,5,6,'Incy'); INSERT INTO pet values (18,5,6,'Wincy'); INSERT INTO pet values (19,5,9,'Daffy'); INSERT INTO pet values (20,6,1,'Woof'); INSERT INTO pet values (21,6,3,'Squeek'); INSERT INTO pet values (22,6,5,'Tony'); INSERT INTO pet values (23,6,7,'Spider'); INSERT INTO pet values (24,6,9,'Jenny'); INSERT INTO pet values (25,7,2,'Jinx'); INSERT INTO visit values (1,1,100,'Broken leg',20.00); INSERT INTO visit values (2,1,100,'Remove cast',5.50); INSERT INTO visit values (3,2,200,'Worming',15.25); INSERT INTO visit values (4,4,100,'Checkup',10.00); INSERT INTO visit values (5,5,100,'checkup',10.00); INSERT INTO visit values (6,6,200,'Worming',15.25); INSERT INTO visit values (7,6,100,'Virus infection',14.60); INSERT INTO visit values (8,6,300,'Eye drops',5.00); INSERT INTO visit values (9,6,400,'Cut eye',12.40); INSERT INTO visit values (10,7,300,'Fin rot',3.00); INSERT INTO visit values (11,7,300,'Fin rot',3.00); INSERT INTO visit values (12,11,200,'checkup',13.00); INSERT INTO visit values (13,11,100,'scale infection',7.00); INSERT INTO visit values (14,12,300,'Eye drops',5.00); INSERT INTO visit values (15,17,400,'infection',3.60); INSERT INTO visit values (16,18,400,'Not eating',3.00); INSERT INTO visit values (17,19,400,'checkup',2.40); INSERT INTO visit values (18,20,300,'Broken tooth',3.00); INSERT INTO visit values (19,20,300,'Infection',3.00); INSERT INTO visit values (20,20,200,'checkup',13.00); INSERT INTO visit values (21,24,100,'Broken wing',17.60); INSERT INTO visit values (22,24,300,'Eye drops',5.00); Many thanks John
