Consider a DB schema consisting of the following relation schemes:
Regions (Region_ID, Region_Name)
Countries (Country_id, Country_Name, Region_Id)
Locations (Location_Id, Street_address, Postal_code, City, State_Province, Country_Id)
Jobs (Job_Id, Job_title, Min_Salary, Max_salary)
Departments (Dep_Id, Dep_Name, Manager_Id, Location_Id)
Employees (Emp_ID, FirstName, Last_Name, E-mail, Phone_number, Hire_date, Job_Id, Salary, Comsn_pct, Manager_Id, Dep_Id)
Employee_History (Emp_ID, Joining_date, last_date, Job_ID, Dep_ID)
6. Create a trigger to ensure that a salary of an employee cannot exceed the salary of his/her manager. If the employee does not have a manager, then his/her salary cannot be more than 10% of the highest salary in the database.
7. For changes in the job of an employee, updated details provided below must be written to Employee History: hire date of the employee for start date, old job ID, old department ID, Employee ID, todays’ system date for end date. In case a row is already present for employee job history then the start date must be the end date of that (row +1).
8. Make a Trigger to ensure that the salary of the employee is never decreased while working in an organization.
9. Create a trigger to ensure that an increase of salary for an employee is conform with the following rules: If experience is more than 8 years, increase salary by max 20%; If experience is greater than 3 years, increase salary by max of 10%; Otherwise a max increase of 5%.
10. Create a trigger to ensure that Min_salary cannot exceed Max_salary for any job.
Expert Answer
6.Create a trigger to ensure that a salary of an employee cannot exceed the salary of
his/her manager. If the employee does not have a manager, then his/her salary cannot
be more than 10% of the highest salary in the database.
// Here you did not mention about the database which you are using.
//In SqlServer
CREATE TRIGGER tri_inssal1 ON [dbo].[Employees]
INSTEAD OF INSERT
AS
declare @emp_id varchar(50);
declare @f_name varchar(100);
declare @l_name varchar(100);
declare @email varchar(100);
declare @phone varchar(15);
declare @hdate datetime;
declare @jobid varchar(50)
declare @emp_sal int;
declare @Comsn_pct varchar(50);
declare @Manager_Id varchar(50);
declare @Dep_Id int;
declare @mgrsal int;
declare @hsal int;
select @emp_id=d.Emp_ID from inserted d;
select @f_name=d.FirstName from inserted d;
select @l_name=d.Last_Name from inserted d;
select @email =d.E-mail from inserted d;
select @phone = d.Phone_number from inserted d;
select @hdate = d.Hire_date from inserted d;
select @jobid = d.Job_Id from inserted d;
select @emp_sal = d.Salary from inserted d;
select @Comsn_pct = d.Comsn_pct from inserted d;
select @Manager_Id = d.Manager_Id from inserted d;
select @Dep_Id = d.Dep_Id from inserted d;
BEGIN
set @mgrsal = (select isnull(salary,0) from Employees where Emp_ID=@Manager_Id)
if(@emp_sal>@mgrsal)
begin
RAISERROR(‘Cannot inset record where employee’s salary more than manager’s salary. ‘,16,1);
ROLLBACK;
end
else if (@mgrsal==0)
begin
set @hsal = (select max(salary) from Employees)
if (@emp_sal>@hsal*10/100)
begin
RAISERROR(‘Cannot inset record where employee’s salary more than 10 perc of the highest salary salary. ‘,16,1);
ROLLBACK;
end
else
begin
insert into Employees (Emp_ID, FirstName, Last_Name, E-mail, Phone_number, Hire_date, Job_Id, Salary, Comsn_pct, Manager_Id, Dep_Id)
values(@emp_id,@f_name,@l_name,@email,@phone,@hdate,@jobid,@emp_sal,@Comsn_pct,@Comsn_pct,@Manager_Id,@Dep_Id);
COMMIT;
end
end
else
begin
insert into Employees (Emp_ID, FirstName, Last_Name, E-mail, Phone_number, Hire_date, Job_Id, Salary, Comsn_pct, Manager_Id, Dep_Id)
values(@emp_id,@f_name,@l_name,@email,@phone,@hdate,@jobid,@emp_sal,@Comsn_pct,@Comsn_pct,@Manager_Id,@Dep_Id);
COMMIT;
end
END
GO
//You can use before trigger if you are using oracle/Mysql to throw the error message.
Eg:-
create trigger tri_inssal1
before insert or update
on Employees
for each row
begin
IF (mgrsal >empsal) THEN
raise_application_error( -20001, ‘No insertion with higher salary.’);
END IF;
end;
8. Make a Trigger to ensure that the salary of the employee is never decreased while working in an organization.
CREATE TRIGGER tri_upsal1 ON [dbo].[Employees]
INSTEAD OF UPDATE
AS
declare @emp_id varchar(50);
declare @f_name varchar(100);
declare @l_name varchar(100);
declare @email varchar(100);
declare @phone varchar(15);
declare @hdate datetime;
declare @jobid varchar(50)
declare @emp_sal int;
declare @Comsn_pct varchar(50);
declare @Manager_Id varchar(50);
declare @Dep_Id int;
declare @sal1 int;
select @emp_id=d.Emp_ID from inserted d;
select @f_name=d.FirstName from inserted d;
select @l_name=d.Last_Name from inserted d;
select @email =d.E-mail from inserted d;
select @phone = d.Phone_number from inserted d;
select @hdate = d.Hire_date from inserted d;
select @jobid = d.Job_Id from inserted d;
select @emp_sal = d.Salary from inserted d;
select @Comsn_pct = d.Comsn_pct from inserted d;
select @Manager_Id = d.Manager_Id from inserted d;
select @Dep_Id = d.Dep_Id from inserted d;
BEGIN
set @sal1 = (select isnull(salary,0) from Employees where Emp_ID=@emp_id)
if(@sal1>@emp_sal)
begin
RAISERROR(‘Cannot insert less salary for the employee ‘,16,1);
ROLLBACK;
end
else
begin
update Employees set Emp_ID=@emp_id, FirstName=@f_name, Last_Name=@l_name, E-mail=@email, Phone_number=@phone, Hire_date=@hdate, Job_Id=@jobid, Salary=@emp_sal, Comsn_pct=@Comsn_pct, Manager_Id=@Manager_Id, Dep_Id=@Dep_Id where Emp_ID=@emp_id;
COMMIT;
end
END
GO
7. For changes in the job of an employee, updated details provided below must be written to Employee History
Note:- Due to lack of time and multiple questions I couldn’t answer for the all questions. Similar to these examples you can complete it easily
CREATE TRIGGER trginsteadupd ON [dbo].[Employees]
INSTEAD OF UPDATE
AS
declare @emp_id varchar(50);
declare @f_name varchar(100);
declare @l_name varchar(100);
declare @email varchar(100);
declare @phone varchar(15);
declare @hdate datetime;
declare @jobid varchar(50)
declare @emp_sal int;
declare @Comsn_pct varchar(50);
declare @Manager_Id varchar(50);
declare @Dep_Id int;
select @emp_id=d.Emp_ID from inserted d;
select @f_name=d.FirstName from inserted d;
select @l_name=d.Last_Name from inserted d;
select @email =d.E-mail from inserted d;
select @phone = d.Phone_number from inserted d;
select @hdate = d.Hire_date from inserted d;
select @jobid = d.Job_Id from inserted d;
select @emp_sal = d.Salary from inserted d;
select @Comsn_pct = d.Comsn_pct from inserted d;
select @Manager_Id = d.Manager_Id from inserted d;
select @Dep_Id = d.Dep_Id from inserted d;
BEGIN
// Query to select last jodID and other details here
//Insert query to Employee_History table here
update Employees set Emp_ID=@emp_id, FirstName=@f_name, Last_Name=@l_name, E-mail=@email, Phone_number=@phone, Hire_date=@hdate, Job_Id=@jobid, Salary=@emp_sal, Comsn_pct=@Comsn_pct, Manager_Id=@Manager_Id, Dep_Id=@Dep_Id where Emp_ID=@emp_id;
COMMIT;
END
GO
Note:- Due to lack of time and multiple questions I couldn’t answer for the all questions. Similar to these examples you can do it easily