SQLite Trigger: AFTER UPDATE

It specifies how to create trigger after update the data. Suppose, we have two tables COMPANY and AUDIT, here we want to keep audit trial for every record being updated in COMPANY table.

COMPANY table:

1. CREATE TABLE COMPANY(  
2.    ID INT PRIMARY KEY     NOT NULL,  
3.    NAME           TEXT    NOT NULL,  
4.    AGE            INT     NOT NULL,  
5.    ADDRESS        CHAR(50),  
6.    SALARY         REAL  
7. );  

Create a new table named AUDIT where log messages will be inserted whenever there is an updation in COMPANY table.

AUDIT table:

1. CREATE TABLE AUDIT(  
2.     EMP_ID INT NOT NULL,  
3.     ENTRY_DATE TEXT NOT NULL  
4. );   

CREATE trigger after update:

Use the following syntax to create a trigger named "after_up" on COMPANY table after update operation.

1.  CREATE TRIGGER after_up AFTER UPDATE   
2. ON COMPANY  
3. BEGIN  
4. INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));  
5. END;  

Now update the old record as following:

1. UPDATE COMPANY SET ADDRESS = 'Noida' WHERE ID = 1;   

See the result:

See the trigger:

1. SELECT name FROM sqlite_master  
2. WHERE type = 'trigger';   

---

SQLite Trigger: BEFORE UPDATE

If you want to create the trigger before updating the data:

1. CREATE TRIGGER befor_up BEFORE UPDATE   
2. ON COMPANY  
3. BEGIN  
4. INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));  
5. END;  

See the triggers:

1. SELECT name FROM sqlite_master  
2. WHERE type = 'trigger';        

SQLite Trigger: AFTER INSERT/ BEFORE INSERT

It specifies how to create trigger after insert the data. Suppose, we have two tables COMPANY and AUDIT, here we want to keep audit trial for every record being inserted in newly created COMPANY table .If you have already a COMPANY table, drop it and create again.

COMPANY table:

1. CREATE TABLE COMPANY(  
2.    ID INT PRIMARY KEY     NOT NULL,  
3.    NAME           TEXT    NOT NULL,  
4.    AGE            INT     NOT NULL,  
5.    ADDRESS        CHAR(50),  
6.    SALARY         REAL  
7. );  

Create a new table named AUDIT where log messages will be inserted whenever there is an entry in COMPANY table for a new record:

AUDIT table:

1. CREATE TABLE AUDIT(  
2.     EMP_ID INT NOT NULL,  
3.     ENTRY_DATE TEXT NOT NULL  
4. );   

CREATE trigger After Insert:

Use the following syntax to create a trigger named "audit_log" on COMPANY table after insert operation.

1. CREATE TRIGGER audit_log AFTER INSERT   
2. ON COMPANY  
3. BEGIN  
4. INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));  
5. END;  

Here, ID is the AUDIT record ID, and EMP_ID is the ID which will come from COMPANY table and DATE will keep timestamp when the record will be created in COMPANY table.

Now insert some record in company table, it will create an audit log record in AUDIT table.

1. INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)  
2. VALUES (1, 'Albert', 22, 'Goa', 40000.00);  

At the same time a record will be created in AUDIT table. This is just because of trigger, which we have created on INSERT operation on COMPANY table. Let's see the AUDIT table.

1. SELECT * FROM AUDIT;   

How to list triggers

You can list down triggers by using sqlite_master statement.

1. SELECT name FROM sqlite_master  
2. WHERE type = 'trigger';   

Output:

You can see the name of the trigger.

You can also list down the trigger on specific table by using AND clause.

1. SELECT name FROM sqlite_master  
2. WHERE type = 'trigger' AND tbl_name = 'COMPANY';    

---

SQLite Trigger: BEFORE INSERT

If you want to create the trigger before inserting the data:

1. CREATE TRIGGER befor_ins BEFORE INSERT   
2. ON COMPANY  
3. BEGIN  
4. INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));  
5. END;  

1. INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)  
2. VALUES (2, 'Sonoo', 28, 'Mumbai', 35000.00);   

You can see that trigger is created already so you can't insert the record.

Check the created trigger:

Here you can see both the created triggers.

SQLite Triggers

SQLite Trigger is an event-driven action or database callback function which is invoked automatically when an INSERT, UPDATE, and DELETE statement is performed on a specified table.

The main tasks of triggers are like enforcing business rules, validating input data, and keeping an audit trail.

Usage of Triggers:

• Triggers are used for enforcing business rules.
• Validating input data.
• Generating a unique value for a newly-inserted row in a different file.
• Write to other files for audit trail purposes.
• Query from other files for cross-referencing purposes.
• Used to access system functions.
• Replicate data to different files to achieve data consistency.

Advantages of using triggers:

• Triggers make the application development faster. Because the database stores triggers, you do not have to code the trigger actions into each database application.
• Define a trigger once and you can reuse it for many applications that use the database.
• Maintenance is easy. If the business policy changes, you have to change only the corresponding trigger program instead of each application program.

---

How to create trigger

The CREATE TRIGGER statement is used to create a new trigger in SQLite. This statement is also used to add triggers to the database schema.

Syntax:

1. CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name   
2. ON table_name  
3. BEGIN  
4.  -- Trigger logic goes here....  
5. END;   

Here, trigger_name is the name of trigger which you want to create.

event_name could be INSERT, DELETE, and UPDATE database operation.

table_name is the table on which you do the operation.

SQLite DELETE Query

In SQLite, DELETE query is used to delete the existing records from a table. You can use it with WHERE clause or without WHERE clause. WHERE clause is used to delete the specific records (selected rows), otherwise all the records would be deleted.

Syntax:

1. DELETE FROM table_name  
2. WHERE [conditions....................];;  

Note: We can use N number of "AND" or "OR" operators with "WHERE" clause.

Example:

We have an existing table named "STUDENT", having the following data:

Example1:

Delete the records of a student from "STUDENT" table where ID is 4.

1. DELETE FROM STUDENT WHERE ID = 4;   

The student's record of id 4 is deleted; you can check it by using SELECT statement:

1. SELECT * FROM STUDENT;  

Output:

Example2:

If you want to delete all records from the table, don't use WHERE clause.

1. DELETE FROM STUDENT;   

You can see that there is no data in the table "STUDENT".

SQLite UPDATE Query

In SQLite, UPDATE query is used to modify the existing records in a table. It is used with WHERE clause to select the specific row otherwise all the rows would be updated.

Syntax:

1. UPDATE table_name  
2. SET column1 = value1, column2 = value2...., columnN = valueN  
3. WHERE [condition];   

Example:

We have an existing table named "STUDENT", having the following data:

Example1:

Update the ADDRESS of the student where ID is

1. UPDATE STUDENT SET ADDRESS = 'Noida' WHERE ID = 1;   

Now the address is updated for the id 1. You can check it by using SELECT statement:

1. SELECT * FROM STUDENT;  

Output:

Example2:

If you don't use WHERE clause, it will modify all address in the STUDENT table:

1. UPDATE STUDENT SET ADDRESS = 'Noida';   

Output: