Untitled

if object_id('EmployeeProject', 'U') is not null 
drop table EmployeeProject 
go 
if object_id('Employee', 'U') is not null 
drop table Employee 
go 
if object_id('Office', 'U') is not null 
drop table Office 
go 
if object_id('Project', 'U') is not null 
drop table Project 
go 
 
 
create table Project ( 
	projectId nchar(5) not null 
		constraint CK_project_projectid 
		check(projectId like '[A-Z][A-Z][0-9][0-9][0-9]') 
		primary key, 
	projectName nvarchar(20) not null unique, 
	budget money null
		constraint CK_project_budget
		check((budget is null) or (budget >= 1000))
) 
go 
 
create table Office ( 
	officeNr smallint primary key, 
	[type] nvarchar(20) not null 
		constraint ck_office_type  
		check([type] in ('Internal', 'Window', 'Corner'))
) 
go  

CREATE TABLE Employee (  
     empId nchar(4) not null  
          constraint CK_employee_empId  
          check(empId like '[0-9][0-9][0-9][0-9]')  
          primary key,  
     empGrade nvarchar(15) not null  
          constraint CK_employee_empGrade  
          check(empGrade in ('Associate', 'Manager', 'Executive')),  
     officeNr smallint null
		  constraint FK_officeNr
	      foreign key references Office(officeNr)
		  on delete set null,
     empName nvarchar(30) not null,  
     street nvarchar(30) null,  
     city nvarchar(30) null,  
     zipCode nchar(5) null  
)  
go  

-- Handle jointly nullable city/state/zipCode 
ALTER TABLE Employee  
add constraint CK_Employee_street_city_zipCode  
check((street is null and city is null and zipCode is null) or (street is not null and city is not null and zipCode is not null))  


create table EmployeeProject( 
	empId nchar(4) not null 
		constraint ck_employeeproject_empid  
		check(empId like '[0-9][0-9][0-9][0-9]') 
		constraint fk_empID  
		foreign key references Employee(empId), 
	projectId nchar(5) not null  
		constraint ck_employee_projectid 
		check (projectID like '[A-Z][A-Z][0-9][0-9][0-9]')  
		constraint fk_projectId  
		foreign key references Project(projectId)
		on delete cascade, 
	primary key(empId, projectId)
) 
go 

create trigger tr_BudgetNotNull
on Project
instead of update
as
if Update (budget) 
	if exists (select * from INSERTED where budget is null and projectId in (select projectId from Project where budget is not null))
		begin
			raiserror ('Once budget has been set to a non-NULL value, it cannot be set back to NULL.', 16, 1)
			rollback Transaction
		end
	else
		begin
			update project set budget = (select budget from INSERTED) where projectId = (select projectId from INSERTED)
		end
go

create trigger tr_employeeproject 
on Employee 
instead of delete 
as 
	if((select count (*) from EmployeeProject where empId = (select empId from Deleted)) = 0)  
		begin
			delete from Employee where empId = (select empId from Deleted) 
		end
	else 
		begin
			raiserror ('Employee already allocated to project.', 16, 1)  
			rollback Transaction 
		end
go

create trigger tr_NoEmployeesOnNullBudget
on EmployeeProject
instead of insert
as
	if exists (select * from INSERTED as i join project as p on i.projectId = p.projectId where p.budget is null)
		begin
			raiserror ('Employees cannot be allocated to projects with NULL budgets.', 16, 1)  
			rollback Transaction 
		end
	else
		begin
			insert into EmployeeProject SELECT empId, projectId from INSERTED
		end