SQLtriggerpractise.sql

create database anjali_1048 
use anjali_1048

---creating department table 
CREATE TABLE Departments
(
   DepartmentID INT PRIMARY KEY,
   DepartmentName VARCHAR(50),
   Location VARCHAR(50)
);

--- creating Employees Table
CREATE TABLE Employees
(
   EmployeeID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   Salary DECIMAL(10,2),
   DepartmentID INT,
   HireDate DATE,
   FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

--- creating Products Table
CREATE TABLE Products
(
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(50),
   Category VARCHAR(50),
   Price DECIMAL(10,2),
   StockQuantity INT
);

--- creating Orders Table
CREATE TABLE Orders
(
   OrderID INT PRIMARY KEY,
   ProductID INT,
   Quantity INT,
   OrderDate DATE,
   FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

--- creating EmployeeAudit Table
CREATE TABLE EmployeeAudit
(
   AuditID INT PRIMARY KEY IDENTITY(1,1),
   EmployeeID INT,
   OldSalary DECIMAL(10,2),
   NewSalary DECIMAL(10,2),
   ChangeDate DATETIME
);

--- GetAllEmployees
CREATE PROCEDURE GetAllEmployees
AS
BEGIN
   SELECT * FROM Employees;
END;
Execution:
EXEC GetAllEmployees;

--- GetEmployeeByID
CREATE PROCEDURE GetEmployeeByID
@EmployeeID INT
AS
BEGIN
   SELECT *
   FROM Employees
   WHERE EmployeeID = @EmployeeID;
END;
Execution:
EXEC GetEmployeeByID 101;

--- GetEmployeesByDepartment
CREATE PROCEDURE GetEmployeesByDepartment
@DepartmentID INT
AS
BEGIN
   SELECT *
   FROM Employees
   WHERE DepartmentID = @DepartmentID;
END;
Execution:
EXEC GetEmployeesByDepartment 1;

--- AddNewEmployee
CREATE PROCEDURE AddNewEmployee
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Salary DECIMAL(10,2),
@DepartmentID INT,
@HireDate DATE
AS
BEGIN
   INSERT INTO Employees
   (FirstName, LastName, Salary, DepartmentID, HireDate)

   VALUES
   (@FirstName, @LastName, @Salary, @DepartmentID, @HireDate);
END;
Execution:
EXEC AddNewEmployee 'Rahul','Sharma',45000,1,'2024-01-10';

--- UpdateEmployeeSalary
CREATE PROCEDURE UpdateEmployeeSalary
@EmployeeID INT,
@NewSalary DECIMAL(10,2)
AS
BEGIN
   UPDATE Employees
   SET Salary = @NewSalary
   WHERE EmployeeID = @EmployeeID;
END;
Execution:
EXEC UpdateEmployeeSalary 101,60000;

--- GetProductsByCategory
CREATE PROCEDURE GetProductsByCategory
@Category VARCHAR(50)
AS
BEGIN
   SELECT *
   FROM Products
   WHERE Category = @Category;
END;
Execution:
EXEC GetProductsByCategory 'Electronics';

--- GetTotalEmployeesByDepartment
CREATE PROCEDURE GetTotalEmployeesByDepartment
AS
BEGIN
   SELECT
   DepartmentID,
   COUNT(*) AS TotalEmployees
   FROM Employees
   GROUP BY DepartmentID;
END;
Execution:
EXEC GetTotalEmployeesByDepartment;

--- TRIGGERS

--- AFTER INSERT Trigger on Employees
CREATE TRIGGER trg_AfterEmployeeInsert
ON Employees
AFTER INSERT
AS
BEGIN
   PRINT 'New Employee Added Successfully';
END;

--- AFTER UPDATE Trigger (Salary Audit)
CREATE TRIGGER trg_LogSalaryChange
ON Employees
AFTER UPDATE
AS
BEGIN
   INSERT INTO EmployeeAudit
   (EmployeeID, OldSalary, NewSalary, ChangeDate)

   SELECT
   d.EmployeeID,
   d.Salary,
   i.Salary,
   GETDATE()

   FROM deleted d
   JOIN inserted i
   ON d.EmployeeID = i.EmployeeID
   WHERE d.Salary <> i.Salary;
END;

---AFTER INSERT Trigger on Orders (Reduce Stock)
CREATE TRIGGER trg_UpdateStockAfterOrder
ON Orders
AFTER INSERT
AS
BEGIN
   UPDATE Products
   SET StockQuantity = StockQuantity - i.Quantity
   FROM Products p
   JOIN inserted i
   ON p.ProductID = i.ProductID;
END;

---INSTEAD OF DELETE Trigger on Products
CREATE TRIGGER trg_PreventProductDelete
ON Products
INSTEAD OF DELETE
AS
BEGIN
   PRINT 'Product deletion is not allowed';
END;