Sunday, October 20, 2019

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.
Sqlite Trigger after insert 1
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);  
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.
  1. SELECT * FROM AUDIT;   
Sqlite Trigger after insert 3
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:
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.
  1. SELECT name FROM sqlite_master  
  2. 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:
  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;  
Sqlite Trigger after insert 6
  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.
Sqlite Trigger after insert 7
Check the created trigger:
Sqlite Trigger after insert 8
Here you can see both the created triggers.

No comments:

Post a Comment

How to DROP SEQUENCE in Oracle?

  Oracle  DROP SEQUENCE   overview The  DROP SEQUENCE  the statement allows you to remove a sequence from the database. Here is the basic sy...