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:

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:

CREATE trigger After Insert:

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

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.

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.

Sqlite Trigger after insert 3

How to list triggers

You can list down triggers by using sqlite_master statement.

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.

Sqlite Trigger after insert 5

SQLite Trigger: BEFORE INSERT

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

Sqlite Trigger after insert 6

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.






Latest Courses