SQLite Trigger Automation

Introduction

In this lab, you will explore SQLite trigger automation. You'll learn how to create, validate, and manage triggers, which automatically execute SQL statements in response to specific events on a table. This lab will guide you through creating audit logs and managing triggers effectively.

Create Tables: employees and employee_audit

In this step, you will create two tables: employees to store employee data and employee_audit to log salary updates.

First, open the SQLite shell in your terminal.

sqlite3 /home/labex/project/test.db

This command opens the SQLite shell and connects to a database file named test.db. If the file doesn't exist, SQLite will create it.

Now, create the employees table with the following structure:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary REAL
);

This SQL command creates a table named employees with three columns:

• id: An integer that serves as the primary key for each employee.
• name: A text field to store the employee's name.
• salary: A real number to store the employee's salary.

Next, create the employee_audit table:

CREATE TABLE employee_audit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    old_salary REAL,
    new_salary REAL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

This table will store a log of salary changes. The columns are:

• id: A unique identifier for each audit entry, automatically incrementing.
• employee_id: The ID of the employee whose salary was changed.
• old_salary: The salary before the update.
• new_salary: The salary after the update.
• updated_at: A timestamp indicating when the update occurred.

Insert Sample Data into employees

In this step, you will insert some sample data into the employees table. This data will be used to test the trigger you will create in the next step.

Execute the following SQL commands to insert three employee records:

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000.00);
INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Charlie', 70000.00);

These commands add three rows to the employees table, with the specified id, name, and salary values.

To confirm that the data was added correctly, run this command to view all records in the table:

SELECT * FROM employees;

Expected Output:

1|Alice|50000.0
2|Bob|60000.0
3|Charlie|70000.0

This output shows the id, name, and salary for each record.

Create a Trigger: salary_update_trigger

Now, you will create a trigger that automatically logs salary updates to the employee_audit table.

Execute the following SQL command to create the trigger:

CREATE TRIGGER salary_update_trigger
AFTER UPDATE OF salary ON employees
BEGIN
    INSERT INTO employee_audit (employee_id, old_salary, new_salary)
    VALUES (OLD.id, OLD.salary, NEW.salary);
END;

This command creates a trigger named salary_update_trigger. This trigger will be executed automatically after any update to the salary column of the employees table.

• AFTER UPDATE OF salary ON employees: Specifies that the trigger will be activated after an update to the salary column in the employees table.
• BEGIN ... END: Encloses the SQL statements to be executed when the trigger is activated.
• INSERT INTO employee_audit ...: Inserts a new record into the employee_audit table with the employee's ID, old salary, and new salary.
  • OLD.id: Refers to the id of the row before the update.
  • OLD.salary: Refers to the salary before the update.
  • NEW.salary: Refers to the salary after the update.

Test the Trigger

In this step, you will test the trigger by updating an employee's salary and then verifying that a new record is added to the employee_audit table.

Let's update Bob's salary:

UPDATE employees SET salary = 65000.00 WHERE id = 2;

This command updates the salary column for the employee with id 2 (Bob) to 65000.00.

Now, query the employee_audit table to see if the trigger worked:

SELECT * FROM employee_audit;

Expected Output:

1|2|60000.0|65000.0|...

You should see a row in the employee_audit table with Bob's employee ID, old salary, and new salary. The updated_at column will show the timestamp of the update. The exact timestamp will vary depending on when you executed the update.

Drop the Trigger

In this step, you will learn how to drop or remove a trigger from your SQLite database.

To drop the salary_update_trigger, execute the following command:

DROP TRIGGER salary_update_trigger;

This command removes the salary_update_trigger from the database.

To verify that the trigger has been dropped, you can query the sqlite_master table:

SELECT name FROM sqlite_master WHERE type='trigger' AND name='salary_update_trigger';

This query should return an empty result set, indicating that the trigger no longer exists.

Now, let's try updating Alice's salary to confirm that the trigger is indeed gone:

UPDATE employees SET salary = 55000.00 WHERE id = 1;

Then, check the employee_audit table:

SELECT * FROM employee_audit;

You should see that no new entry has been added to the employee_audit table, confirming that the trigger has been successfully dropped.

Finally, exit the SQLite shell:

.exit

Summary

In this lab, you have learned how to create SQLite triggers that automatically execute SQL statements in response to update events on a specific table. You created an employees table and an employee_audit table. You then created a trigger named salary_update_trigger that logs salary updates to the employee_audit table. Finally, you learned how to drop a trigger.