SQLite Trigger simplifies your database operations

preface

Life is not just about what you get, it’s Ctrl + C and V

Often we come across this need:

You need to keep track of what the user browses; Or search history, which needs to be updated every time you search; . In general, we need to create a history table his_table and check whether this data already exists before each insert. The tricky point is that each insert requires a Query to determine the next inser or update operation. Even more complex business, after modifying table A, we need to modify the data of table B, so that we need to operate two tables, making our code more bloated. Database triggers can solve this kind of problem, today will introduce SQLite3 database triggers in Android use.Copy the code

Show your code!!

SQLite trigger

A Trigger is a database callback function that executes automatically when a specified database event occurs

Let’s say our data construction statement looks like this

CREATE TABLE db_list_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    item_id INTEGER NOT NULL.date TimeStamp DEFAULT (datetime('now'.'localtime')));Copy the code

Date TimeStamp DEFAULT (datetime()) date TimeStamp DEFAULT (datetime())'now'.'localtime'))
Copy the code

You think this is the end of it? (ಡ ಡ omega) (ಡ omega ಡ)

 CREATE TRIGGER auto_remove BEFORE INSERT
    ON db_list_table
    BEGIN
        DELETE FROM db_list_table WHERE NEW.user_id=user_id AND NEW.item_id=item_id;
    END;
Copy the code

Create a trigger that removes the same old data before each insert. The following explains what each field means.

SQLite triggers are database callback functions that are automatically executed when a specified database event occurs

• SQLite only supports FOR EACH ROW triggers, not FOR EACH STATEMENT triggers, so you don’t explicitly declare FOR EACH ROW
• You can specify that it is triggered when a DELETE, INSERT, or UPDATE occurs in a particular database table, or when the columns of one or more specified tables are updated
• The BEFORE or AFTER keywords determine when the trigger action is executed, and whether the trigger action is executed BEFORE or AFTER the associated row is inserted, modified, or deleted
• If a WHEN statement is provided, the SQL statement is executed only on the specified row where the WHEN statement is true. If no WHEN statement is provided, the SQL statement is executed for all rows
• Both the WHEN statement and the trigger action can use new.column-name and old.column-name to refer to the value of the current operation row, where column-name is the column name of the table associated with the trigger
• Triggers are automatically dropped when the tables associated with them are dropped
• The table to be modified must exist in the same database as the table or view to be attached as a trigger

Create trigger

We can take apart this create trigger SQL

 CREATE TRIGGER// Create trigger auto_remove // Trigger name, which can be used to query and remove triggers laterBEFORE// Fire before the event, insteadAFTERIt's a later triggerINSERT// The insertion event trigger is also supportedDELETE,UPDATE
 ONDb_list_table // Which table to operate onBEGINDelete user_id and item_id from db_list_table where user_id and item_id are insertedDELETE FROM db_list_table WHERE user_id=NEW.user_id ANDitem_id=NEW.item_id; // Don't forget the semicolon // because the trigger event isINSERT, so the form data should use new.column -nameReference; // May be more round, you taste, you fine taste, is not very reasonable (ಡωಡ)END; // Trigger statement terminationCopy the code

English documentation for the NEW and OLD keywords

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name".where column-name is the name of a column from the table that the trigger is associated with.
OLD and NEW references may only be used in triggers on events for whichthey are relevant, as follows: INSERT NEW references are valid UPDATE NEW and OLD references are valid DELETE OLD references are // INSERT, UPADATE, DELETE refers to the triggering action type, not the triggering statement type. That's what happens AFTER BEFOR/AFTERCopy the code

After creating this trigger, it automatically retrieves the existing data every time new data is inserted, deletes it if it exists, and then inserts it again. This way we don’t need to wait for the insertion and update logic to be handled automatically by triggers.

We can also use triggers to update the data of other tables. For example, we do a backup of the database data, and record all the insert records of the main table.

// Create a backup tableCREATE TABLE db_list_backup_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    item_id INTEGER NOT NULL.date TimeStamp NOT NULL); // Create a backup triggerCREATE TRIGGER back_up AFTER INSERT 
ON db_list_table 
    BEGIN 
        INSERT INTO db_list_backup_table (user_id,item_id,date) VALUES(NEW.user_id,NEW.item_id,NEW.date); // The trigger action isINSERT, the use ofNEWKeyword referencevalue
    END;

Copy the code

Insert some data to see the backup effect

As you may have noticed, the trigger statement between BEGIN and END is an SQL delete statement. Yes, the trigger statement is a normal delete statement. You can also insert default values (such as more detailed timestamps), and even use more complex SQL for more advanced functions. But it also has some limitations:

• Tables to be modified in UPDATE, DELETE, or INSERT statements only support tables under the same database
• The ORDER BY and LIMIT clauses of UPDATE and DELETE statements are not supported
• The ‘INSERT INTO table_name DEFAULT VALUES’ form of INSERT statements is not supported

For example, the product manager added a requirement to automatically delete invalid data when data is inserted, without panic, just by modifying the trigger. However, the trigger cannot be modified and can only be created after being deleted

Delete trigger

drop triggerTrigger_name // Delete by trigger nameCopy the code

CREATE TRIGGER auto_remove BEFORE INSERT
    ON db_list_table
    BEGIN
        DELETE FROM db_list_table WHERE 
            strftime('%s'.'now') - strftime('%s'.date) > =30 // 30The data before S is invalidatedOR 
            (NEW.user_id=user_id AND NEW.item_id=item_id);
    END;
Copy the code

// Convert current time to seconds strftime('%s'.'now')
Copy the code

Query trigger

// query through sqlite_master, not query our own representationSELECT name FROM sqlite_master WHERE type = 'trigger';
Copy the code

Use transactions when executing multiple SQL statements to ensure that the statement executes as expected

db.beginTransaction()
try {
    db.execSQL("sql a")
    db.execSQL("sql b")
    // Database transaction succeeded
    db.setTransactionSuccessful()
} catch (e: SQLException) {
    e.printStackTrace()
    // todo exception handling
} finally {
    db.endTransaction()
}
Copy the code

conclusion

• Use transactions when executing multiple SQL statements to ensure that the statement executes as expected (nonsense)
• The SQLite Trigger is a database callback function that is automatically executed before and after a DELETE, INSERT, or UPDATE occurs in a database table
• Trigger statements, while powerful, have limitations and do not support all syntax
• You can use new. column-name and old. column-name to refer to the value of the current operation line
• Triggers can be added or deleted, but not easily modified. Remember to migrate triggers if the table structure changes

Application screenshots

The project source has been uploaded to Github

If there are any mistakes or deficiencies, welcome to correct

Making email [email protected]

End

A link to the

SQLite trigger documentation SQLite time function documentation

SQLite time functions

SQLite English documentation