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:

snippet

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

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:

snippet

CREATE TABLE AUDIT(
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
);

CREATE trigger After Insert:

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

snippet

CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
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.

snippet

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
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.

snippet

SELECT * FROM AUDIT;

How to list triggers

You can list down triggers by using sqlite_master statement.

snippet

SELECT name FROM sqlite_master
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.

snippet

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

SQLite Trigger: BEFORE INSERT

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

snippet

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

snippet

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
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.