![]() ![]() However, the log has been saved to the account_changes table: SELECT * FROM account_changes Code language: SQL (Structured Query Language) ( sql ) SQLite issued an error due to not enough balance: Abort due to constraint violation ( CHECK constraint failed: accounts) Code language: CSS ( css ) VALUES( 100, '-', 20000,datetime( 'now')) Code language: SQL (Structured Query Language) ( sql ) SET balance = balance - 20000 WHERE account_no = 100 Let’s take another example of rolling back a transaction.įirst, attempt to deduct 20,000 from account 100: BEGIN TRANSACTION Sixth, query the contents of the account_changes table: SELECT * FROM account_changes Code language: SQL (Structured Query Language) ( sql ) VALUES( 200, '+', 1000,datetime( 'now')) ĬOMMIT Code language: SQL (Structured Query Language) ( sql )įifth, query data from the accounts table: SELECT * FROM accounts Code language: SQL (Structured Query Language) ( sql )Īs you can see, balances have been updated successfully. ![]() INSERT INTO account_changes(account_no,flag,amount,changed_at) SET balance = balance + 1000 WHERE account_no = 200 SET balance = balance - 1000 WHERE account_no = 100 Third, query data from the accounts table: SELECT * FROM accounts Code language: SQL (Structured Query Language) ( sql )įourth, transfer 1000 from account 100 to 200, and log the changes to the table account_changes in a single transaction. INSERT INTO accounts (account_no,balance)Ĭode language: SQL (Structured Query Language) ( sql ) INSERT INTO accounts (account_no,balance) Second, insert some sample data into the accounts table. ![]() ) Code language: SQL (Structured Query Language) ( sql ) The account_changes table stores the changes of the accounts.įirst, create the accounts and account_changes tables by using the following CREATE TABLE statements: CREATE TABLE accounts ( The accounts table stores data about the account numbers and their balances. We will create two new tables: accounts and account_changes for the demonstration. If you do not want to save the changes, you can roll back using the ROLLBACK or ROLLBACK TRANSACTION statement: ROLLBACK Code language: SQL (Structured Query Language) ( sql ) SQLite transaction example COMMIT Code language: SQL (Structured Query Language) ( sql ) Third, commit the changes to the database by using the COMMIT or COMMIT TRANSACTION statement. Note that the change is only visible to the current session (or client). Second, issue SQL statements to select or update data in the database. BEGIN TRANSACTION Code language: SQL (Structured Query Language) ( sql )Īfter executing the statement BEGIN TRANSACTION, the transaction is open until it is explicitly committed or rolled back. To start a transaction explicitly, you use the following steps:įirst, open a transaction by issuing the BEGIN TRANSACTION command. It means that for each command, SQLite starts, processes, and commits the transaction automatically. On the contrary, if the program crashes before the transaction is committed, the change should not persist.īy default, SQLite operates in auto-commit mode.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |