Quiz #4 Solutions


1.
a) alter table States add constraint DTValue check (donationTax >=0 );

b) alter table Donations add constraint NotSame check (dnrSSN <> empSSN);

2.
a) create trigger OnNewDonor after insert on Donors
	referencing
		new row as newRow
	for each row
	begin
		if (IsNull(newRow.stateAbbrev)
			update Donors
			set stateAbbrev = "FL"
			where ssn = newRow.ssn;
	end;
	
b) create trigger OnNewDonation before insert on Donations
	referencing
		new row as nR
	for each row
	begin
		if exists(select *
				from donations d
				where d.dnrssn = nR.dnrssn
				and   d.value = nR.value
				and   d.date = nR.date)
			raise error ('Possible Duplicate Donation');
	end;

3.
a) create procedure AddArticleDonation(in nvalue decimal, in ndate Date,
				in ndnrssn char[9], in nempssn char[9],
				in nname char[20], in nnumber integer,
				in nnotes varchar[250])
	declare
		newDid integer;
	begin
		newDid = select getNewDid();
		
		insert into Donations(did,value,date,dnrssn,empssn)
		values (newDid,nvalue,ndate,ndnrssn,nempssn);
		
		insert into Articles(did,name,number,notes)
		valueus (newDid,nname,nnumber,nnotes);
	end;