DBMS - Trigger and Schema Modification

Triggers and schema modifications are quite useful in managing and automating database operations. Triggers are special programs that automatically run when certain events take place in a database, like inserting, updating, or deleting data. Schema modification, on the other hand, focuses on changing the structure of the database itself. Schema modifications are used for adding or removing tables, columns, or constraints. Read this chapter to learn how to use triggers for automation and how to modify database schemas.

Sample Tables and Data

In the examples of this chapter, we will use an e-commerce database. The associated tables and the data are given below −

Given below is the Customers table −

Here is the Products table −

The Orders table is as follows −

Triggers in SQL

Triggers are like "event listeners" in a database. They run automatically when specific actions take place in a table. The operations could be anything like INSERT, UPDATE, or DELETE.

Creating a Trigger

First of all, one must understand how to create a trigger. In the following example, when a new order is placed, the stock quantity for the ordered product should automatically decrease.

CREATE TRIGGER UpdateStock
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
   UPDATE Products
   SET stock_quantity = stock_quantity - NEW.quantity
   WHERE product_id = NEW.product_id;
END;

This trigger automatically updates the stock quantity after processing new orders. It does the following −

• The AFTER INSERT trigger runs whenever a new row is added inside the Orders table.
• The NEW keyword refers to the newly inserted row.
• Finally, the trigger updates the Products table by reducing the stock for the ordered product.

Adding an Order

When an order is placed for a product, the Orders table gets updated −

INSERT INTO Orders (order_id, customer_id, product_id, quantity, order_date)
VALUES (4, 1, 102, 3, '2023-11-05 10:00:00');

The trigger updates the Products table and now it would be as follows −

Here, we can see the stock_quantity for Smartphone decreases from 20 to 17 after the order is placed.

Deleting a Trigger

If we no longer need a trigger, we can simply remove it. We can drop an existing trigger using the DROP TRIGGER statement.

DROP TRIGGER UpdateStock;

It removes the UpdateStock trigger from the database.

Schema Modification in SQL

Schema modification refers to altering the structure of the database, which can include adding or removing tables, columns, or constraints.

Adding a New Table

Let's create an Employees table to track the employees in the system −

CREATE TABLE Employees (
   employee_id INT PRIMARY KEY AUTO_INCREMENT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   position VARCHAR(50),
   salary DECIMAL(10, 2),
   hire_date DATE DEFAULT CURRENT_DATE
);

The Employees Table Schema looks as follows −

The new table is now ready for storing employee data.

Modifying an Existing Table

Sometimes we may want to update a table by adding or removing its columns. The following example demonstrates how you can add a column for loyalty points.

Example: Add a Column for Loyalty Points

To track customer loyalty points, let us see how to add a column to the Customers table −

ALTER TABLE Customers
ADD loyalty_points INT DEFAULT 0;

The updated Customers table would be as follows −

Removing a Column

If a column is no longer needed, we can simply drop it. Use the following query to remove the loyalty_points column from the Customers table −

ALTER TABLE Customers
DROP COLUMN loyalty_points;

The updated Customers table would be as follows −

Renaming a Table

To rename a table, we can use the RENAME TABLE statement. Use the following query to rename the Orders table to CustomerOrders −

RENAME TABLE Orders TO CustomerOrders;

The table Orders is now called CustomerOrders.

Dropping a Table

If a table is no longer needed, we can remove it entirely. You can use the following query to drop the Employees table −

DROP TABLE Employees;

It deletes the Employees table along with all its data.

When to Use Triggers and Schema Modifications?

We generally use triggers for automation, like updating related data or maintaining logs. One must avoid overusing triggers because they can make debugging harder.

Schema Modifications are used while adapting to new business requirements. One must be cautious when dropping columns or tables. As it may lead to data loss.

Conclusion

In this chapter, we understood how to use triggers and schema modifications for managing databases. We learnt how triggers can automate tasks, like updating stock quantities after an order is placed. We also explored the role of schema modifications in adding new tables, modifying existing ones, and removing unnecessary structures.