SQL LAB @ HOME 11 ~ NIIT POST

sql lab @ home11

1.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.

ANS.

CREATE TRIGGER updTrigger
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

---

3.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. (Use the AdventureWorks database)

ANS.

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 average value of 

       rate cannot be more than 20'

    ROLLBACK TRANSACTION

END

END

---

4.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. (Use the AdventureWorks database)

ANS.

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 under the reorder point'
END

---

5.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. (Use the AdventureWorks database)

ANS.

CREATE TRIGGER Emp_Update
ON EmployeeDetails 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
before 9 A.M and after 5 P.M')
END
END

---

6.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?

ANS.

CREATE TRIGGER Vendoracccountnum
ON Purchasing.vendor
FOR UPDATE AS
BEGIN
DELCARE @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

---

7.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. (Use the AdventureWorks database)

ANS.

CREATE TRIGGER Emp_Update_Trg
ON EmployeeDetails AFTER UPDATE
AS
BEGIN
DECLARE @OldSal MONEY
DECLARE @NewSal MONEY
SELECT @OldSal = Salary FROM DELETED
SELECT @NewSal = Salary FROM INSERTED
IF @OldSal > @NewSal
BEGIN
ROLLBACK
PRINT ('New salary cannot be less than the old salary')
END
END

---

9.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. (Use the AdventureWorks database)

ANS.

CREATE TRIGGER Emp_Nested_Trg
ON EmployeeDetails AFTER INSERT
AS
BEGIN
DECLARE @DptNo INT
SELECT @DptNo = DeptNo FROM INSERTED
IF NOT EXISTS (SELECT * FROM DeptDetails
WHERE DeptNo = @DptNo)
PRINT 'The specified DeptNo does not exist.'
ROLLBACK
END

---

10.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.

ANS.

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

---

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.

ANS.

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