SQL Server Stored Procedures and Triggers for Database Operations and Auditing

Description:

This project demonstrates the creation and usage of SQL Server stored procedures and triggers for managing data operations, enforcing business rules, and implementing audit logging across two sample databases: ITI DB and Company DB.

The script includes:

Stored Procedures

Retrieving the number of students per department.

Checking the number of employees in a specific project and returning conditional messages with employee details.

Updating project assignments when replacing an old employee with a new one.

Table Modifications and Audit Implementation

Adding a Budget column to the Project table.

Creating an Audit table to log budget changes, including the project number, user who performed the update, date of modification, and old/new budget values.

Triggers for Data Control and Logging

Logging budget updates in the Project table.

Preventing new record insertions into the Department table.

Preventing insertions into the Employee table during the month of March.

Logging insert operations in the Student table to a StudentAudit table with server username, date, and a descriptive note.

Logging delete attempts in the Student table to the StudentAudit table instead of actually deleting records.

This project highlights practical examples of database constraints, automated auditing, and customized data manipulation control using SQL Server features.