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.

Sqlite Trigger after insert 1

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);
Sqlite Trigger after insert 2

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;
Sqlite Trigger after insert 3

How to list triggers

You can list down triggers by using sqlite_master statement.

snippet
SELECT name FROM sqlite_master
WHERE type = 'trigger';

Output:

Sqlite Trigger after insert 4

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 after insert 5

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;
Sqlite Trigger after insert 6
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.

Sqlite Trigger after insert 7

Check the created trigger:

Sqlite Trigger after insert 8

Here you can see both the created triggers.

Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents