STEP 6
PART A: TRIGGERS
Create a trigger to enforce the business rule that the minimum salary must be less than the maximum salary.

We start by deciding that the SQL select statement we want to always have a 0 result set from is such:

select minimumsalary, maximumsalary from job where minimumsalary>maximumsalary  //we do not want to allow this

in the context of a trigger the code looks like:

Create Trigger JobUpdate23 
on job 
for insert, update 
AS 
IF EXISTS 
(
select minimumsalary, maximumsalary 
FROM job 
WHERE minimumsalary>maximumsalary
)
 BEGIN 
RAISERROR('minimum salary must be less than maximum salary.',15,1) 
ROLLBACK TRAN 
END


The results are that when I try to add a record to the job table where the minimum salary exceeds the maximum salary I get an error message returned.

Insert into job values ('R2D2', 'Astromech', '12000', '1200')

result
Msg 50000, Level 15, State 1, Procedure JobUpdate23, Line 1
minimum salary must be less than maximum salary.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.


Create a trigger to enforce the business rule that the start date of a salary must be before a known end date.

So we want to deny any results set from occurring in the select statement:

Select  statdate, enddate from salaryhistory where startdate > enddate



Packaging this inside trigger code is more of the same from above:

Create Trigger SalaryUpdate23 
on salaryhistory
for insert, update 
AS 
IF EXISTS 
(
Select  startdate, enddate
FROM salaryhistory 
WHERE startdate > enddate
)
 BEGIN 
RAISERROR('start date must precede end date.',15,1) 
ROLLBACK TRAN 
END

The results are that when I try to add a record to the salaryhistory table where the start date exceeds the end date I get an error message returned.

Example:
update salaryhistory
set startdate = '1991-02-01'
where employeeid = '24-22' and
startdate = '1990-02-01';

Msg 50000, Level 15, State 1, Procedure SalaryUpdate23, Line 12
start date must precede end date.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.


PART B 
STORED PROCEDURES
Create a stored procedure that given an employee number will return the employees first name, last name, job title, and department.

I took it as “you are only still employed if you end date in the salaryhistory table is NULL”
This is the convention for “employed” I use through the assignment.

We want to start with the select statement:
select employee.firstname, employee.lastname, departmentname, job.jobtitle from employee, job, jobhistory, department where jobhistory.deptcode = department.deptcode and employee.employeeid = jobhistory.employeeid and (jobhistory.jobcode = job.jobcode) and (jobhistory.endjob is NULL)

this will give us ALL the active employees and their info.


But to pass a parameter to get info for a specific employee id:

Create PROCEDURE sp_JobInfo23 @empid CHAR(5) AS select Create PROCEDURE sp_JobInfo23 @empid CHAR(5) AS select employee.firstname, employee.lastname, departmentname, job.jobtitle from employee, job, jobhistory, department where jobhistory.deptcode = department.deptcode and employee.employeeid = jobhistory.employeeid and (jobhistory.jobcode = job.jobcode) and (jobhistory.endjob is NULL and employee.employeeid = @empid)

The result when you type :
sp_JobInfo23  ’24 - 22’
firstname	lastname	department		jobtitle
------------------------------------------------------------------------------
NN23              	Scott             	Personnel         	Supervisor