1 / 11

Triggers and Transactions

Triggers and Transactions. Making Decisions behind the scene. Overview. Majority of databases are used behind some form of front-end application Applications can be written to contain DBMS rules for data flow and storage

mareo
Download Presentation

Triggers and Transactions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Triggers and Transactions Making Decisions behind the scene

  2. Overview • Majority of databases are used behind some form of front-end application • Applications can be written to contain DBMS rules for data flow and storage • Allowing DBMS to administer rules is more efficient and less costly when changes to rules are required • DBMS can act on data when it is received from host application

  3. What Controls the Flow? DBMS DBMS applies data to tables based on standard rules Application contains code to maintain Data Integrity. Additional point of failure created. Data Integrity may be at risk due additional point of failure DBMS DBMS tests data against User defined rules and makes decision on when/how data is processed Application gathers and sends raw data to DBMS Data Integrity maintained

  4. Triggers • Often used to maintain Data Integrity. Also used to perform tests on business rules (eg: customer credit limits) • External rules designed to test the effect Inserting, Updating and Deleting data will have on data tables • Can manipulate data based on outcome of the test • Can be applied before or after data tables have been altered • Code stored in DBMS and will automatically activate when a pre-defined event occurs (data inserted into table) • Generally execute faster than front-end application code • Triggers can only handle one type of operation at a time (Insert, Update or Delete). • Triggers do not work on Views (or temporary tables)

  5. Triggers Table: sales Table: cust_cred Note: Customer A132 is about to make a new purchase for $300.

  6. Creating Triggers Trigger will activate before data is added to the sales table. What would happen if ‘Before’ was changed to ‘After’? Create Trigger cust_cred_limit Before Insert on sales For Each Row Begin Declare tot_owing as Integer Declare cred_limit as Integer Select Sum(sales) into tot_owing From Sales Where cust_ID = “A132” and paid =0; Select limit From cust_cred INTO cred_limit Where cust_ID = “A132”; If cred_limit > tot_owing Then Insert Into Sales(sales_ID, cust_ID, sales, sale_date, paid); End If; End$$ Set-up variables to hold temporary data. Total the sales for the customer, but only those invoices that have not been paid. Place calculated value into variable Get the credit limit value for the customer Test to see if the customer has not exceeded their credit limit. If not then add the sale to the table Note: If the credit limit has been exceeded, the sale was not added.

  7. Transactions • Transactions are extremely important, in terms of data integrity • Transactions vital when data affects multiple tables • Determines if tables will be altered or queries will be aborted due to some failure (power, code violation, etc) Transaction Keywords Begin - Starts transaction process. All subsequent queries will be executed, but data will not be permanently written to tables Commit - Tells system to make all query alterations permanent. Rollback – Tells system to remove alterations from the tables. (Note: If the Commit command has already been executed, Rollback will not work)

  8. Transactions Pending Queries Add sale to ‘sales’ table Add commission to ‘commissions table’. This requires a calculation (sales x commission %) Both queries must be successful to complete the transaction

  9. Transactions Mysql: mysql> BEGIN; mysql> Insert Into Sales Values(sales_ID, cust_ID, sales, sale_date, paid, emp_ID); Mysql> Insert Into commissions Values(sales_ID, emp_ID, comm_amt); mysql> ROLLBACK; Without a COMMIT statement, the insert was not permanent, and was reversed with the ROLLBACK. Note that the added record was visible during the transaction from the same user account that created it. No other user would see the changes until transaction was committed. Mysql: mysql> BEGIN; mysql> Insert Into Sales Values(sales_ID, cust_ID, sales, sale_date, paid, emp_ID); Mysql> Insert Into commissions Values(sales_ID, emp_ID, comm_amt); mysql> Commit; With a COMMIT statement, the insert becomes permanent. All other users can now see changes.

  10. Transactions What happens if the the computer fails(power failure, etc.) in the middle of transaction? mysql> BEGIN; mysql> Insert Into Sales Values(sales_ID, cust_ID, sales, sale_date, paid, emp_ID); Mysql> Insert Into commissions Values(sales_ID, emp_ID, comm_amt); mysql> Commit; Power Failure!! Because the Commit command was never executed, all queries are automatically reversed. Without a Transaction, data integrity would have been lost.

  11. Transactions You can test Transactions from the Command Line By default MySQL automatically commits queries that are not part of a transaction. To change this, type the following; SET AUTOCOMMIT = O; Now, all queries will react as if you had typed BEGIN, to start a transaction. To complete your queries you have to type COMMIT; To turn AUTOCOMMIT back on, type; SET AUTOCOMMIT = 1; Now your queries will function, as before.

More Related