Sunday, October 20, 2019

SQLite Trigger: AFTER UPDATE

It specifies how to create trigger after update the data. Suppose, we have two tables COMPANY and AUDIT, here we want to keep audit trial for every record being updated in COMPANY table.

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 updation in COMPANY table.

AUDIT table:
  1. CREATE TABLE AUDIT(  
  2.     EMP_ID INT NOT NULL,  
  3.     ENTRY_DATE TEXT NOT NULL  
  4. );   
CREATE trigger after update:
Use the following syntax to create a trigger named "after_up" on COMPANY table after update operation.
  1.  CREATE TRIGGER after_up AFTER UPDATE   
  2. ON COMPANY  
  3. BEGIN  
  4. INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));  
  5. END;  
Sqlite Trigger after update 1
Now update the old record as following:
  1. UPDATE COMPANY SET ADDRESS = 'Noida' WHERE ID = 1;   
Sqlite Trigger after update 2
See the result:
Sqlite Trigger after update 3
See the trigger:
  1. SELECT name FROM sqlite_master  
  2. WHERE type = 'trigger';   
Sqlite Trigger after update 4

SQLite Trigger: BEFORE UPDATE

If you want to create the trigger before updating the data:
  1. CREATE TRIGGER befor_up BEFORE UPDATE   
  2. ON COMPANY  
  3. BEGIN  
  4. INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));  
  5. END;  
Sqlite Trigger after update 5
See the triggers:
  1. SELECT name FROM sqlite_master  
  2. WHERE type = 'trigger';        
Sqlite Trigger after update 6

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...