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 −

customer_id	first_name	last_name	email	phone
1	Anu	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

Here is the Products table −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5

The Orders table is as follows −

order_id	customer_id	product_id	quantity	order_date
1	1	101	1	2023-11-01 10:00:00
2	2	103	2	2023-11-02 12:30:00
3	3	102	1	2023-11-03 15:45:00

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.

CREATETRIGGER UpdateStock
AFTERINSERTON Orders
FOR EACH ROWBEGINUPDATE 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 −

INSERTINTO 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 −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	17
103	Office Chair	Furniture	150.00	5

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.

DROPTRIGGER 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 −

CREATETABLE Employees (
   employee_id INTPRIMARYKEYAUTO_INCREMENT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   position VARCHAR(50),
   salary DECIMAL(10,2),
   hire_date DATEDEFAULTCURRENT_DATE);

The Employees Table Schema looks as follows −

employee_id	first_name	last_name	position	salary	hire_date
(empty)

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 −

ALTERTABLE Customers
ADD loyalty_points INTDEFAULT0;

The updated Customers table would be as follows −

customer_id	first_name	last_name	email	phone	loyalty_points
1	Anu	Joshi	[email protected]	123-456-7890	0
2	Bimal	Saha	[email protected]	234-567-8901	0
3	Chandan	Tudu	[email protected]	345-678-9012	0

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 −

ALTERTABLE Customers
DROPCOLUMN loyalty_points;

The updated Customers table would be as follows −

customer_id	first_name	last_name	email	phone
1	Anu	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

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 −

RENAMETABLE 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 −

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