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.

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

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.

How to list triggers

You can list down triggers by using sqlite_master statement.

Output:

You can see the name of the trigger.

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

SQLite Trigger: BEFORE INSERT

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

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.