IADRT Lab@home

01. The management of AdventureWorks Incorporation wants that whenever the pay rate of an employee is modified, its effect on the monthly salary of that employee should be displayed. John, the Database Developer at AdventureWorks, has been asked to resolve this problem. Help John to find an appropriate solution.

create Trigger uptrigger
on HumanResources.EmployeePayHistory for UPdate
as
Begin
declare @rate as money
Declare @frq as int
select @rate=Rate,
@frq=payfrequency from Inserted
select @rate*@frq*30 as 'Monthly salary'
end

Update HumanResources.EmployeePayHistory
set rate=rate+5
Where EmployeeID=160


02. The management of AdventureWorks Incorporation has decided that no user should be able to change the prices of products. In addition, the management wants that all the attempts to change the price should be saved in a temporary table, Temp. John, the Database Developer, has been asked to make significant changes in the database to implement this policy. What should John do to achieve the same?

create Table Temp
(
ProductID int,
AttChangeCost money,
AttTime datetima
)


create trigger updtrigger on
Production.ProductCostHistory insert of UPdate
as
Begin
declare @Pid as int
Declare @cost as money
select @pid=ProductID,
@cost=standardCost from Deletedinsert into Temp values(@Pid,@cost,getdate())
select 'sorry you can not change the price of a product'
end


update Production.ProductCostHistory
set standardCost=55
where productID=707


03. Create a trigger on the Product table to ensure that if the value for the SafetyStockLevel column becomes less than or equal to the specified ReorderPoint column for a product because of an update operation, a message informing that the reorder level has been attained should be displayed.

create Trigger OrderStock
on Production.Product
for update
as
declare @PID varchar
select @PID=ProductID from inserted
if((select safetyStockLevel from INserted)<=
(select ReorderPoint from inserted))
Begin
print'The stock of'+@PID+
'is unber the reorder point'
end


04. Create a trigger to ensure that the average of the values in the Rate column of the EmployeePayHistory table should not be more than 20 when the value of the rate is increased.

create trigger UpdatetriggerEPayHistory
on HumanResources.EmployeePayHistory
for update
as
if Update (rate)
begin
declare @AvgRate float
select @avgRate=Avg (Rate)
from HumanResources.EmployeePayHistory
if (@avgRate>20)
Begin
Print 'The averge value of rate connot be more than  20'
Rollback Transaction
end
end04.


05. The management of AdventureWorks wants that whenever the account number of a user is modified, the old account number, as well as the new account number, should be instantaneously displayed to the user. How can you accomplish the desired task?

create Trigger Vendoraccountnum
on Purchasing.Vendor
for Update as
Begin
Declare @oldvalue nvarchar(15)
Declare @newvalue nvarchar(15)
select @oldvalue=Accountnumber from deleted
select @Newvalue=Accountnumber from inserted
print 'The old account number is'+@oldvalue
Print 'The New account number is'+Newvalue
end


06. Create a trigger named Emp_Update on the EmployeeDetail table. This trigger should restrict a user from performing any kind of DML operation on the table before 9 A.M and after 5 P.M.

create trigger emp_UPdate
on employeeDetail After insert, UpDATE, Delete
as
Begin
Declare @Time INT
set @Time=DateName (HH,GETDATE())
if @Time not Between 9 and 17
Begin
Rollback
print ('The operation cannot be performed')
end
end


07. Create a trigger named Emp_Update_Trg that will restrict the updation of salary of an employee if the new salary is less than the previous salary in the EmployeeDetails table.

create trigger emp_UPdate_Trg
on employeeDetail After UpDATE
as
Begin
Declare @oldSal money
Declare @NewSal money
select @oldSal=salary from deleted
select @NewSal=Salary from Insertd
if @OldSal>@NewSal
begin
Rollback
print ('New salary cannot be less than the old salary')
end
end


08. Create a trigger named Emp_Delete_Trg on the EmployeeDetails table. This trigger should restrict the deletion of records from the table if the designation of an employee is Manager.

create trigger emp_Delete_Trg
on employeeDetails After Delete
as
Begin
Declare @job varchar(50)
select @job=Designation from deleted
if @job='Manager'
begin
Rollback
print ('Cannot Delete Manager from The Table')
end
end


09. Consider the following statement: CREATE VIEW Emp_Dept AS SELECT E.EmployeeID, E.EmpName, E.Designation, E.Salary, D.DeptNo, D.DeptName FROM EmployeeDetails E INNER JOIN DeptDetails D ON E.DeptNo=D.DeptNo A view named Emp_Dept has been created on the base tables, EmployeeDetails and DeptDetails, by using the preceding statement. John is the Database Developer with AdventureWorks Incorporation. He wants that the users should be able to insert data in the underlying base tables by using the view. He can implement the desired functionality if the insert operation affects a single table. However, the same cannot be done if multiple tables are involved. He wants to be able to insert data in both the tables with the help of a single insert statement. Help John in resolving the problem.

create trigger View_Insert_Trg
on Emp_Dept Instead of Insert
as
Begin
Insert into EmployeeDetails(EmployeeID,EMPName,
Designation, Salary,DeptNo)
select EmployeeID,EmpName,Designation,salary,DeptNo from Inserted
Insert into DeptDetails(DeptNo,DeptName)
select DeptNo,DeptName from Inserted
end


10. Create a trigger named Emp_Nested_Trg on the EmployeeDetails table for an insert operation. This trigger should ensure that a new record being inserted into the EmployeeDetails table has a matching record in the DeptDetails table. Otherwise, the insert operation is rolled back.

create trigger Emp_Nestes_Trg
on EmployeeDetails After Insert
as
Begin
Declare @OptNo int
select @OptNo=OptNo from Inserted
If not Exists(select * from DeptDetails
Where DeptNo=@deptNo)
Print 'The specified DeptNo does not exist.'
Rollback
end

11. John is a Database Developer. He is not able to delete the unwanted records from the base tables by using the view, Emp_Dept. Help John in resolving the problem.

create trigger view_delete_trg
on Emp_Dept Instead of delete
as
Begin
Declare @DeptNo int
declare @Count int
select @DeptNo=DeptNo from deleted
select @Count=Count(*) from EmployeeDetails
Where DeptNo=@DeptNo
delete from EmployeeDetails Where DeptNo=@DeptNo
if @Count=1
Delete from deptdetails where DeptNo=@deptNo
end






1 comment:

Subscribe to: Posts (Atom)