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