Triggers (MySQL Version) CREATE TRIGGER <trigger name> {BEFORE | AFTER} CS 235: {INSERT | UPDATE | DELETE} Introduction to Databases ON <table name> FOR EACH ROW Svetlozar Nestorov <SQL statements> Lecture Notes #14 Example Options • Whenever we insert a new tuple into Sells, • AFTER triggers cannot change the value make sure the beer mentioned is also of the inserted/updated tuple. mentioned in Beers, and insert it (with a null manufacturer) if not. • BEFORE triggers can change the value of the inserted/updated tuple. CREATE TRIGGER BeerTrig AFTER INSERT ON Sells FOR EACH ROW BEGIN INSERT IGNORE INTO Beers(name) VALUES(new.beer); END; More Options Explanation • INSERT can be DELETE or UPDATE • There are two special (transition) variables new and old , representing the new and old tuple in • FOR EACH ROW can be omitted, with the change. an important effect: the action is done – old makes no sense in an insert, and new makes no once for the relation(s) consisting of all sense in a delete. changes. • MySQL recognized only “FOR EACH ROW” 1
More Explanations Even More Explanations • The action is any statement allowed in a MySQL • Important MySQL constraint: the action function cannot change the relation that triggers – Simplest form: surround one or more SQL statements the action. with BEGIN and END. • MySQL returns an error only at run time. – However, select-from-where has a limited form. • Need to (temporarily) redefine default delimiter (;) to another character, e.g. ($) • MySQL triggers are part of the database schema, like tables or views. Example Attribute Checks with Triggers • Maintain a list of all the bars that raise their price for • Create two triggers BEFORE INSERT some beer by more than $1. RipoffBars(bar) and BEFORE UPDATE DELIMITER // – What about BEFORE DELETE? CREATE TRIGGER PriceTrig • The triggers check attribute constraint AFTER UPDATE ON Sells FOR EACH ROW and if not satisfied make a modification BEGIN IF (NEW.price > OLD.price + 1) THEN that will be rejected, so the triggering INSERT INTO RipoffBars VALUES(NEW.bar); INSERT or UPDATE will fail. END IF; END; // DELIMITER ; Example Example CREATE TRIGGER PriceInsTrig CREATE TABLE Sells ( BEFORE INSERT ON Sells bar CHAR(20) NOT NULL, FOR EACH ROW beer CHAR(20), BEGIN price REAL; IF (NEW.price > 12) THEN ); SET NEW.bar = NULL; END IF; • Check that the price is not more than $12. END; // 2
Example SQL Triggers CREATE TRIGGER PriceUpdTrig • Covered in the book. BEFORE UPDATE ON Sells • Some differences, including: FOR EACH ROW 1. The MySQL restriction about not modifying BEGIN the relation of the trigger or other relations IF (NEW.price > 12) THEN linked to it by constraints is not present in SET NEW.bar = NULL; SQL. END IF; 2. The action in SQL is a list of (restricted) SQL END; // statements. DB Application Programming Interface Solutions • Application is written in general-purpose 1. Extend SQL with general-purpose programming language: C, C++, Java… programming: PSM. – Not in SQL! 2. Execute DB queries within application • Application-driven database queries. code: embedded SQL. – E.g., user registers, sends a message. 3. Call function from DB library: call-level • Impedance mismatch: interface (CLI), ODBC, JDBC. – Sets (relations) are first class objects in DBMS, but not in C, Java… – Vice versa for pointers, conditional statements. Persistent Stored Modules Basic PSM Form: Procedures • Stored procedures as DB elements. CREATE PROCEDURE <name> ( • Combine general-purpose programming <parameters>) with SQL. <declarations> • Extends functionality of DBMS. <body>; 3
Basic PSM Form: Functions Parameters in PSM CREATE FUNCTION <name> ( • For each parameter: – Mode: IN, OUT, INOUT <parameters>) RETURNS <type> – Name: as usual <declarations> – Type: as usual <body>; • Examples: IN newprice NUMBER OUT oldprice NUMBER INOUT drinker VARCHAR[30] Example Invoking Procedures • A procedure to add a beer and price to • Using SQL/PSM command CALL Spoon’s menu: CALL spoonMenu(‘BudHeavy’, ‘7.50’) CREATE PROCEDURE spoonMenu( • Functions can be used in SQL IN beer VARCHAR[30], expressions, provided that the return type IN price NUMBER is appropriate. ) INSERT INTO Sells VALUES(‘Spoon’, beer, price); PSM Statements IF Statements • DECLARE <name> <type>; • Simplest form: • SET <variable> = <expression> IF <condition> THEN <statements> END IF; • BEGIN <statements> END • With ELSE: • RETURN <expression> IF…THEN…ELSE…END IF; – Does not terminate execution! • Nested: IF…THEN…ELSEIF…ELSEIF…ELSE…END IF; 4
Loops • Basic form: LOOP <statements> END LOOP; • Exiting loops: <loop name>: LOOP …LEAVE <loop name>… END LOOP; • Other forms: WHILE <cond> DO <stmts> END WHILE; REPEAT <stmts> UNTIL <cond> END REPEAT; 5
Recommend
More recommend
