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 PROCEDURE