Creating SQL Triggers

Description

This project demonstrates the use of SQL triggers to automate inventory management and track price changes in a product database. In Part 1, a trigger updates the Product_ReOrder field when inventory levels fall below a set threshold. In Part 2, a trigger logs changes to product pricing by capturing old and new values, change dates, and product IDs in an audit_log table. The project highlights essential SQL skills, including table creation, data manipulation, and trigger-based automation for inventory control and data auditing.

Languages Used

• SQL

Project walk-through:

Part 1

The first step of this project was to CREATE a TABLE for “Products”. We did so by utilizing the following query:

After creating the table, the next step was to insert values into the Products table. We added 'PowerSupplies'
and 'Hard Disks' as the initial products, along with the corresponding values for each.

Now that the last command was successful, we used the SELECT statement to retrieve and verify the inserted
values in the Products table.

Next, we created a trigger on the Product_ReOrder field,as its initial value was set to 0—making it easy to verify
whether the trigger executed successfully.

Then, we updated the Product_QOH and Product_MIN values to meet the trigger condition and activate the query.

Finally, we used the SELECT statement to view the Products table and confirmed that the Product_ReOrder value
successfully changed from 0 to 1.

Part 2

The first step is to create a new table called audit_log to record the old and new product prices, the date of each
change,and the corresponding product ID.

Next, we created a trigger on the Price_History to capture and log price changes.

Then, we updated the Products table by setting a new list price of 50 for the product with a code of 1.

Finally, we used the SELECT statement to view the audit_log table and verify the recorded changes.