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