Database Triggers: Event-Condition-Action Mechanism for Automating Database Operations, Study notes of Logic

Download Database Triggers: Event-Condition-Action Mechanism for Automating Database Operations and more Study notes Logic in PDF only on Docsity!

ADVANCED SQL DDL

CS121: Relational Databases

Fall 2018 – Lecture 10

Advanced SQL DDL

¨ Last time, covered stored procedures and user-defined

functions (UDFs)

¤ Relatively simple but powerful mechanism for extending

capabilities of a database

¤ Most databases support these features (in different ways, of

course…)

¨ Today, will cover three more advanced features of SQL

data definition

¤ Triggers

¤ Materialized views (briefly)

¤ Security constraints in databases

2

Triggers (2)

¨ If the database handles audit-log updates automatically

and independently:

¤ Application code doesn’t become more complex by introducing audit functionality ¤ Audit log will be a more trustworthy record of modifications to bank account records

¨ Triggers are used for many other purposes, such as:

¤ Preventing invalid changes to table data ¤ Automatically updating timestamp values, derived attributes, etc. ¤ Executing business rules when data changes in specific ways

n e.g. place an order for more parts when current inventory dips below

a specific value

¤ Replicating changes to another table, or even another database 4

Trigger Mechanism

¨ DB trigger mechanism must keep track of two things:

¨ When is the trigger actually executed?

¤ The event that causes the trigger to be considered

¤ The condition that must be satisfied before the trigger

will execute

n (Not every database requires a condition on triggers…)

¨ What does the trigger do when it’s executed?

¤ The actions performed when the trigger executes

¨ Called the event-condition-action model for triggers

5

When Triggers Execute

¨ Can typically execute the trigger before or after the

triggering DML event

¤ Usually, DDL/user/database triggering events only run the trigger after the event (pretty obvious) ¤ “Before” triggers can abort the DML operation, if necessary

¨ Some DBs also support “instead of” triggers

¤ Execute trigger instead of performing the triggering operation

¨ Triggers are row-level triggers or statement-level triggers

¤ A row-level trigger is executed for every single row that is modified by the statement

n (…as long as the row satisfies the trigger condition, if specified…)

¤ A statement-level trigger is executed once for the entire statement 7

Trigger Data

¨ Row-level triggers can access the old and new

version of the row data, when available:

¤ Insert triggers only get the new row data

¤ Update triggers get both the old and new row data

¤ Delete triggers only get the old row data

¨ Triggers can also access and modify other tables

¤ e.g. to look up or record values during execution

8

Trigger Example: Bank Overdrafts

¨ Want to handle overdrafts on bank accounts

¨ If an update causes a balance to go negative:

¤ Create a new loan with same ID as the account number

¤ Set the loan balance to the negative account balance

n (…the account balance went negative…)

¤ Need to update borrower table as well!

¨ Needs to be a row-level trigger, executed before

or after updates to the account table

¤ If database supports trigger conditions, only trigger on

updates when account balance < 0

10

SQL99/Oracle Trigger Syntax

¨ Book uses SQL:1999 syntax, similar to Oracle/DB

CREATE TRIGGER trg_overdraft AFTER UPDATE ON account

REFERENCING NEW ROW AS nrow

FOR EACH ROW WHEN nrow.balance < 0

BEGIN ATOMIC

INSERT INTO loan VALUES (nrow.account_number,

nrow.branch_name,

• nrow.balance);

INSERT INTO borrower

(SELECT customer_name, account_number

FROM depositor AS d

WHERE nrow.account_number = d.account_number);

UPDATE account AS a SET balance = 0

WHERE a.account_number = nrow.account_number;

END

11

MySQL INSERT Enhancements

¨ MySQL has several enhancement to the INSERT command

¤ (Most databases provide similar capabilities)

¨ Try to insert a row, but if key attributes are same as another

row, simply don’t perform the insert:

INSERT IGNORE INTO tbl ...;

¨ Try to insert a row, but if key attributes are same as another

row, update the existing row:

INSERT INTO tbl ... ON DUPLICATE KEY UPDATE attr1 = value1 , ...;

¨ Try to insert a row, but if key attributes are same as another

row, replace the old row with the new row

¤ If key is not same as another row, perform a normal INSERT REPLACE INTO tbl ...; 13

MySQL Trigger Syntax (2)

CREATE TRIGGER trg_overdraft BEFORE UPDATE ON account FOR EACH ROW BEGIN DECLARE overdraft_fee NUMERIC(12, 2) DEFAULT 30; DECLARE overdraft_amt NUMERIC(12, 2); -- If an overdraft occurred then handle by creating/updating a loan. IF NEW.balance < 0 THEN -- Remember that NEW.balance is negative. SET overdraft_amt = overdraft_fee - NEW.balance; INSERT INTO loan (loan_number, branch_name, amount) VALUES (NEW.account_number, NEW.branch_name, overdraft_amt) ON DUPLICATE KEY UPDATE amount = amount + overdraft_amt; INSERT IGNORE INTO borrower (customer_name, loan_number) SELECT customer_name, account_number FROM depositor WHERE depositor.account_number = NEW.account_number; SET NEW.balance = 0; END IF; END; 14

Alternatives to Triggers

¨ Triggers can be used to implement many complex tasks

¨ Example: Can implement referential integrity with

triggers!

¤ On all inserts and updates to referencing table, ensure that

foreign-key column value appears in referenced table

n If not, abort the operation!

¤ On all updates and deletes to referenced table, ensure that

value doesn’t appear in referencing table

n If it does, can abort the operation, or cascade changes to the referencing relation, etc.

¨ This is definitely slower than the standard mechanism J

16

Alternatives to Triggers (2)

¨ Can you use stored procedures instead?

¤ Stored procedures usually have fewer limitations than

triggers

n Stored procs can take more detailed arguments, return values to indicate success/failure, have out-params, etc. n Can perform more sophisticated transaction processing

¤ Trigger support is also very vendor-specific, so either

implementation choice will have this limitation

¨ Typically, triggers are used in very limited ways

¤ Update “row version” or “last modified timestamp” values in

modified rows

¤ Simple operations that don’t require a great deal of logic

¤ Database replication (sometimes)

17

Materialized Views

¨ Some databases provide materialized views, which

implement such functionality

¨ Simple views usually treated as named SQL queries

¤ i.e. a derived relation with the specified definition

¨ When a query refers to a simple view, database

substitutes view’s definition directly into the query

¤ Benefit: allows optimization of the entire query

¤ Drawback: if many queries reference a simple view,

the same values will be computed again and again…

19

Materialized Views (2)

¨ Materialized views actually create a new table,

populated by the results of the view definition

¤ Queries can use values in the materialized view over

and over, without recomputing

¤ Database can perform optimized lookups against the

materialized view, e.g. by using indexes

¨ Just one little problem:

¤ What if the tables referenced by the view change?

¤ Need to recompute contents of the materialized view!

¤ Called view maintenance

20