Latest web development tutorials

SQLite Trigger

SQLite database trigger is a callback function, it will automatically execute when the call / specified database event occurs. The following are the main points on the trigger SQLite: SQLiteTrigger (Trigger) is the callback function of the database, it will automatically execute / call when a specified database event occurs.The following are the main points about SQLite triggers (Trigger) of:

  • SQLite triggers (Trigger) triggers can be specified at the time of the occurrence of a specific database tables DELETE, INSERT or UPDATE, or triggered when one or more specified columns of a table update occurs.

  • SQLite supports only FOR EACH ROW triggers (Trigger), not FOR EACH STATEMENT triggers (Trigger). Therefore, explicitly specify FOR EACH ROW is optional.

  • WHEN clause and the trigger (Trigger) action may be accessed using a formNEW.column-name and OLD.column-namereferences inserted, deleted or updated row elements, column-name is associated with the trigger from the table columns The name.

  • WHEN clause, if provided, is true only for the WHEN clause specifies the rows to execute SQL statements. If no WHEN clause is executed SQL statements for all rows.

  • After the implementation of the trigger action BEFORE or AFTER keyword determines when the trigger action, the decision is inserted in the associated lines, or before you delete or modify.

  • When the table associated with the trigger deleted automatically delete trigger (Trigger).

  • To modify the table must exist in the same database as the trigger is attached tables or views, and must use only thetablename, instead database.tablename.

  • A special SQL function RAISE () can be used to trigger an exception within the program.

grammar

Create atrigger (Trigger) The basic syntax is as follows:

CREATE TRIGGER trigger_name [BEFORE | AFTER] event_name 
ON table_name
BEGIN
 - Trigger logic goes here ....
END;

Here, event_name can be mentioned in the table table_nameofINSERT, DELETE and UPDATEdatabase operations. You can optionally specify FOR EACH ROW after the table name.

The following are specified in the UPDATE operation in one or more tables on the columns create a trigger (Trigger) syntax: 

CREATE TRIGGER trigger_name [BEFORE | AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 - Trigger logic goes here ....
END;

Examples

Let us assume a situation where we want to be inserted into the newly created table COMPANY (if it already exists, delete the re-created) in each of the record-keeping audit test: 

sqlite> CREATE TABLE COMPANY (
   ID INT PRIMARY KEY NOT NULL,
   NAME TEXT NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (50),
   SALARY REAL
);

In order to maintain audit trials, we will create a new table called AUDIT. Whenever COMPANY table has a new record entry, the log message will be inserted: 

sqlite> CREATE TABLE AUDIT (
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
);

Here, ID is AUDIT record ID, EMP_ID the ID from the COMPANY table, DATE will keep timestamp is created when COMPANY is recorded. So, now let us create a trigger on the COMPANY table as follows: 

sqlite> CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT (EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime ( 'now'));
END;

We will now start to insert a record in the COMPANY table, this will lead to the creation of an audit logging AUDIT table. So, let's create a record in the COMPANY table, as follows: 

sqlite> INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00);

This will create a record in the following table COMPANY: 

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0

At the same time, we will create a record in the AUDIT table. This record is the result of a trigger, which is the trigger we INSERT operations on tables created COMPANY (Trigger). Similarly, can create triggers on UPDATE and DELETE operations (Trigger). 

EMP_ID ENTRY_DATE
---------- -------------------
12013-04-05 06:26:00

Lists trigger (TRIGGERS)

You can list all triggers fromsqlite_master table, as follows:

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';

The above SQLite statement lists only one entry, as follows: 

name
----------
audit_log

If you want to list the trigger on a specific table, use the AND clause to join tables were as follows: 

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

The above SQLite statement lists only one entry, as follows: 

name
----------
audit_log

Remove the trigger (TRIGGERS)

The following is a DROP command can be used to remove the existing trigger: 

sqlite> DROP TRIGGER trigger_name;