Sqlite Views and Trigger Operations

In SQLite, views and triggers are used for different purposes but are both powerful tools for managing and manipulating data.

Views

Views are virtual tables that represent the result of a SELECT statement. They allow you to simplify complex queries by storing them as a view that can be queried like a table. Views do not store any data themselves; they simply present the data from the underlying tables in a specified format. Views can also be used to restrict access to certain columns or rows of a table.

Let’s create a table like below;

CREATE TABLE Employees (
    ID INTEGER PRIMARY KEY,
    Name TEXT,
    Department TEXT,
    Salary INTEGER
);

We can create a view table like below;

CREATE VIEW HighSalaries AS
SELECT * FROM Employees WHERE Salary > 50000;

Created view HighSalaries that selects all columns from the Employees table where the Salary is greater than 50000. This view will effectively filter out all rows where the Salary is not greater than 50000, showing only those employees with salaries higher than 50000.

Let’s add a few elements to the table;

INSERT INTO Employees (Name, Department, Salary) VALUES ('Ahmet', 'IT', 60000);
INSERT INTO Employees (Name, Department, Salary) VALUES ('Burak', 'Embedded', 30000);
INSERT INTO Employees (Name, Department, Salary) VALUES ('Burhan', 'Embedded', 90000);

We expect those whose salaries are greater than 50000 to be included in the view table.

We can get the view table with the following command;

SELECT * FROM HighSalaries;

Like we did in the example above. If we want to create a virtual table for elements with a certain condition and read this table when we need it, creating a view is very convenient for us.

Triggers

Triggers are special stored procedures that are automatically executed in response to certain events on a table, such as INSERT, UPDATE, or DELETE operations. Triggers can be used to enforce business rules, maintain referential integrity, or log changes to a table.

Let’s make an example for the table we created above. Assume we need a logging mechanism triggered by a salary change.

We can create the trigger as follows;

CREATE TABLE IF NOT EXISTS SalaryChangeLog (
    LogID INTEGER PRIMARY KEY,
    EmployeeID INTEGER,
    OldSalary INTEGER,
    NewSalary INTEGER,
    ChangeDate TEXT
);

CREATE TRIGGER IF NOT EXISTS AfterSalaryChange
AFTER UPDATE OF Salary ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO SalaryChangeLog (EmployeeID, OldSalary, NewSalary, ChangeDate)
    VALUES (OLD.ID, OLD.Salary, NEW.Salary, datetime('now'));
END;

we first create a table called SalaryChangeLog to store logs of salary changes for employees. It has columns for a log ID (primary key), the employee ID (to link to the employee), the old salary, the new salary, and the date of the change.

Then, we define a trigger called AfterSalaryChange that activates after an update operation (AFTER UPDATE OF Salary) on the Salary column of the Employees table. For each row that is updated, the trigger inserts a new row into the SalaryChangeLog table, capturing the EmployeeID, OldSalary, NewSalary, and the current date and time (datetime(‘now’).

This trigger whenever an employee’s Salary field in the Employees table is updated, it logs this change to the SalaryChanges table;

UPDATE Employees SET Salary = 20000 WHERE ID = 1;