create table Appointments(
id int not null identity(1,1),
usernameClient varchar(32),
usernameInstructor varchar(32) not null,
carLicense varchar(6),
confirmed varchar(12),
idTimeSlot int not null,
slotDate date not null,
slotTime time(0) not null,
primary key (id),
foreign key (usernameClient) references Clients (username),
foreign key (usernameInstructor) references Instructors (username),
foreign key (carLicense) references Cars (license),
foreign key (idTimeSlot, slotDate, slotTime) references TimeSlots (id, slotDate, slotTime),
check (confirmed = 'allocated' or confirmed = 'confirmed' or confirmed = 'driven'or confirmed = 'paid'))
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('adele', 'bdozer', 'BCD101', '2', '2017-06-12', '07:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('adele', 'bdozer', 'BCD101', '2', '2017-06-12', '08:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('dave', 'agator', 'BCD202', '1', '2017-06-12', '09:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mal', 'bdozer', 'BCD101', '2', '2017-06-12', '09:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('king', 'mfone', 'CDE101', '4', '2017-06-12', '09:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('dave', 'agator', 'FGH202', '1', '2017-06-12', '10:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mal', 'bdozer', 'BCD101', '2', '2017-06-12', '10:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('king', 'mfone', 'CDE101', '4', '2017-06-12', '10:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('david', 'agator', 'BCD101', '1', '2017-06-12', '11:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('anna', 'kpilla', 'FGH101', '3', '2017-06-12', '11:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('rih', 'mfone', 'CDE101', '4', '2017-06-12', '11:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('david', 'agator', 'BCD101', '1', '2017-06-12', '12:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('anna', 'kpilla', 'FGH101', '3', '2017-06-12', '12:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('katy', 'bdozer', 'BCD101', '2', '2017-06-12', '13:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tonic', 'kpilla', 'FGH101', '3', '2017-06-12', '13:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('miss', 'mfone', 'CDE101', '4', '2017-06-12', '13:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('grah', 'agator', 'BCD101', '1', '2017-06-12', '14:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('katy', 'bdozer', 'BCD202', '2', '2017-06-12', '14:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('miss', 'mfone', 'CDE101', '4', '2017-06-12', '14:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('jem', 'agator', 'BCD101', '1', '2017-06-12', '15:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tiki', 'bdozer', 'BCD202', '2', '2017-06-12', '15:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('jem', 'agator', 'BCD101', '1', '2017-06-12', '16:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tiki', 'bdozer', 'BCD202', '2', '2017-06-12', '16:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('holls', 'kpilla', 'FGH101', '3', '2017-06-12', '16:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('holls', 'kpilla', 'FGH101', '3', '2017-06-12', '17:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('anna', 'kpilla', 'FGH101', '3', '2017-06-12', '18:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('anna', 'kpilla', 'FGH101', '3', '2017-06-12', '19:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('adele', 'bdozer', 'BCD202', '2', '2017-06-13', '07:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('adele', 'bdozer', 'BCD202', '2', '2017-06-13', '08:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('river', 'agator', 'BCD101', '1', '2017-06-13', '09:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('king', 'mfone', 'CDE101', '4', '2017-06-13', '09:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('river', 'agator', 'BCD101', '1', '2017-06-13', '10:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('keyboard', 'bdozer', 'BCD202', '2', '2017-06-13', '10:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('king', 'mfone', 'CDE101', '4', '2017-06-13', '10:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('jem', 'agator', 'BCD101', '1', '2017-06-13', '11:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bret', 'kpilla', 'FGH202', '3', '2017-06-13', '11:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('jem', 'agator', 'BCD101', '1', '2017-06-13', '12:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bret', 'kpilla', 'FGH202', '3', '2017-06-13', '12:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bohboh', 'bdozer', 'BCD202', '2', '2017-06-13', '13:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('dave', 'kpilla', 'FGH202', '3', '2017-06-13', '13:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('miss', 'mfone', 'CDE101', '4', '2017-06-13', '13:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('david', 'bdozer', 'BCD202', '2', '2017-06-13', '14:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('dave', 'kpilla', 'FGH202', '3', '2017-06-13', '14:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('miss', 'mfone', 'CDE101', '4', '2017-06-13', '14:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mal', 'bdozer', 'BCD202', '2', '2017-06-13', '15:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('rih', 'mfone', 'CDE101', '4', '2017-06-13', '15:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mal', 'bdozer', 'BCD202', '2', '2017-06-13', '16:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('grah', 'kpilla', 'FGH202', '3', '2017-06-13', '17:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('anna', 'kpilla', 'FGH202', '3', '2017-06-13', '18:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('anna', 'kpilla', 'FGH202', '3', '2017-06-13', '19:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('adele', 'bdozer', 'BCD101', '2', '2017-06-14', '07:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('adele', 'bdozer', 'BCD101', '2', '2017-06-14', '08:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bret', 'agator', 'BCD202', '1', '2017-06-14', '09:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bret', 'mfone', 'FGH202', '4', '2017-06-14', '09:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bret', 'agator', 'BCD202', '1', '2017-06-14', '10:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('keyboard', 'bdozer', 'BCD101', '2', '2017-06-14', '10:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bret', 'mfone', 'FGH202', '4', '2017-06-14', '10:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('river', 'agator', 'BCD202', '1', '2017-06-14', '11:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('holls', 'kpilla', 'FGH101', '3', '2017-06-14', '11:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bohboh', 'mfone', 'FGH202', '4', '2017-06-14', '11:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('river', 'agator', 'BCD202', '1', '2017-06-14', '12:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('holls', 'kpilla', 'FGH202', '3', '2017-06-14', '12:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tiki', 'bdozer', 'BCD101', '2', '2017-06-14', '13:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('david', 'kpilla', 'FGH202', '3', '2017-06-14', '13:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bob', 'mfone', 'FGH101', '4', '2017-06-14', '13:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tiki', 'bdozer', 'BCD101', '2', '2017-06-14', '14:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('david', 'kpilla', 'FGH202', '3', '2017-06-14', '14:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bob', 'mfone', 'FGH101', '4', '2017-06-14', '14:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tiki', 'bdozer', 'BCD101', '2', '2017-06-14', '15:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('katy', 'mfone', 'FGH202', '4', '2017-06-14', '15:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tiki', 'bdozer', 'BCD101', '2', '2017-06-14', '16:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('billy', 'kpilla', 'FGH202', '3', '2017-06-14', '16:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('katy', 'mfone', 'FGH101', '4', '2017-06-14', '16:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('billy', 'kpilla', 'FGH202', '3', '2017-06-14', '17:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('anna', 'kpilla', 'FGH202', '3', '2017-06-14', '18:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('anna', 'kpilla', 'FGH202', '3', '2017-06-14', '19:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mal', 'bdozer', 'CDE101', '2', '2017-06-15', '07:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mal', 'bdozer', 'CDE101', '2', '2017-06-15', '08:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('river', 'agator', 'BCD101', '1', '2017-06-15', '09:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('river', 'agator', 'BCD101', '1', '2017-06-15', '10:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('keyboard', 'bdozer', 'CDE101', '2', '2017-06-15', '10:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('dave', 'mfone', 'FGH202', '4', '2017-06-15', '10:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('jem', 'agator', 'BCD101', '1', '2017-06-15', '11:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tonic', 'kpilla', 'FGH101', '3', '2017-06-15', '11:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('dave', 'mfone', 'FGH202', '4', '2017-06-15', '11:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('jem', 'agator', 'BCD101', '1', '2017-06-15', '12:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('holls', 'bdozer', 'FGH101', '2', '2017-06-15', '13:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('rih', 'mfone', 'FGH202', '4', '2017-06-15', '13:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('holls', 'bdozer', 'FGH101', '2', '2017-06-15', '14:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('grah', 'mfone', 'FGH202', '4', '2017-06-15', '14:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('billy', 'mfone', 'FGH202', '4', '2017-06-15', '15:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tiki', 'bdozer', 'FGH202', '2', '2017-06-15', '16:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tonic', 'kpilla', 'FGH101', '3', '2017-06-15', '16:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('billy', 'mfone', 'BCD202', '4', '2017-06-15', '16:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('dave', 'kpilla', 'FGH101', '3', '2017-06-15', '19:00:00', 'driven')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('david', 'bdozer', 'FGH202', '2', '2017-06-16', '07:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('david', 'bdozer', 'FGH202', '2', '2017-06-16', '08:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bret', 'agator', 'BCD101', '1', '2017-06-16', '09:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bob', 'bdozer', 'FGH202', '2', '2017-06-16', '09:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('katy', 'mfone', 'BCD202', '4', '2017-06-16', '09:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bret', 'agator', 'BCD101', '1', '2017-06-16', '10:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bob', 'bdozer', 'FGH202', '2', '2017-06-16', '10:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('king', 'mfone', 'BCD202', '4', '2017-06-16', '10:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tonic', 'kpilla', 'FGH202', '3', '2017-06-16', '11:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('king', 'mfone', 'BCD202', '4', '2017-06-16', '11:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mal', 'bdozer', 'FGH101', '2', '2017-06-16', '13:00:00', 'paid')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('rih', 'mfone', 'BCD202', '4', '2017-06-16', '13:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('holls', 'agator', 'BCD101', '1', '2017-06-16', '14:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('keyboard', 'bdozer', 'FGH101', '2', '2017-06-16', '14:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('rih', 'mfone', 'BCD202', '4', '2017-06-16', '14:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('holls', 'agator', 'BCD101', '1', '2017-06-16', '15:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('billy', 'bdozer', 'FGH101', '2', '2017-06-16', '15:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('grah', 'mfone', 'BCD202', '4', '2017-06-16', '15:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('river', 'agator', 'BCD101', '1', '2017-06-16', '16:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('billy', 'bdozer', 'FGH101', '2', '2017-06-16', '16:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('tonic', 'kpilla', 'FGH202', '3', '2017-06-16', '16:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('grah', 'mfone', 'BCD202', '4', '2017-06-16', '16:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('river', 'agator', 'BCD101', '1', '2017-06-16', '17:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('billy', 'mfone', 'FGH202', '1', '2017-06-17', '09:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('billy', 'mfone', 'FGH202', '1', '2017-06-17', '10:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bohboh', 'mfone', 'FGH202', '1', '2017-06-17', '11:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bret', 'mfone', 'FGH202', '1', '2017-06-17', '14:00:00', 'confirmed')
insert into appointments(usernameClient, usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bret', 'mfone', 'FGH202', '1', '2017-06-17', '15:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH101', '3', '2017-06-12', '14:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mfone', 'CDE101', '4', '2017-06-12', '15:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mfone', 'CDE101', '4', '2017-06-12', '16:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD101', '1', '2017-06-12', '17:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bdozer', 'BCD202', '2', '2017-06-13', '09:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mfone', 'CDE101', '4', '2017-06-13', '11:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD101', '1', '2017-06-13', '14:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD101', '1', '2017-06-13', '15:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD101', '1', '2017-06-13', '16:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH202', '3', '2017-06-13', '16:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mfone', 'CDE101', '4', '2017-06-13', '16:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD101', '1', '2017-06-13', '17:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bdozer', 'BCD101', '2', '2017-06-14', '09:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD202', '1', '2017-06-14', '14:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD202', '1', '2017-06-14', '15:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD202', '1', '2017-06-14', '16:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD202', '1', '2017-06-14', '17:00:00', 'confirmed')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bdozer', 'CDE101', '2', '2017-06-15', '09:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mfone', 'FGH202', '4', '2017-06-15', '09:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH101', '3', '2017-06-15', '12:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH101', '3', '2017-06-15', '13:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD101', '1', '2017-06-15', '14:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH101', '3', '2017-06-15', '14:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD101', '1', '2017-06-15', '15:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('bdozer', 'FGH202', '2', '2017-06-15', '15:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD101', '1', '2017-06-15', '16:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD101', '1', '2017-06-15', '17:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH101', '3', '2017-06-15', '17:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH101', '3', '2017-06-15', '18:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD101', '1', '2017-06-16', '11:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('agator', 'BCD101', '1', '2017-06-16', '12:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH202', '3', '2017-06-16', '12:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH202', '3', '2017-06-16', '13:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH202', '3', '2017-06-16', '14:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH202', '3', '2017-06-16', '17:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH202', '3', '2017-06-16', '18:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('kpilla', 'FGH202', '3', '2017-06-16', '19:00:00', 'allocated')
insert into appointments(usernameInstructor, carLicense,idtimeslot,slotDate,slotTime,confirmed) values('mfone', 'FGH202', '1', '2017-06-17', '13:00:00', 'allocated')上面就是表和数据，下面是问题
Once bookings have been driven they cannot be deleted. Write a trigger called LockDriven to ensure no driven bookings can be removed from the Appointments table.
Write 2 safe tests for question 6. One safe must test a single appointment deletion. The other safe test must test for multiple appointment deletions.
Sample Response for safe tests
Msg 50000, Level 16, State 1, Procedure LockDriven, Line 5
You cannot remove a driven booking
Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.
drop trigger LockDriven
create trigger LockDriven
on Appointments
for delete
as
declare @cou int=0
select @cou=COUNT(confirmed) from deleted
if @cou=0 return
if @cou>0 begin
Raiserror('Sample Response for safe tests',16,1)
Raiserror('You cannot remove a driven booking',16,1)
rollback tran
return
end这是我写的但不管我删除的是不是driven的他都挡下了。
CREATE TRIGGER LockDriven
ON Appointments
INSTEAD OF delete
AS
BEGIN
SET NOCOUNT ON;
delete from Appointments
where id in (
select id
from deleted
where confirmed<>'driven'
)
END触发器有三个触发方式关键字，for|after|instead of
可以去了解下这几个触发模式的区别