triggers n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
TRIGGERS PowerPoint Presentation
play fullscreen
1 / 10

TRIGGERS

278 Views Download Presentation
Download Presentation

TRIGGERS

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. TRIGGERS

  2. WHY TRIGGERS ARE USEFUL • A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. • Triggers in MySQL can be associated with “insert”, “update” and “delete” statements. • TRIGGERS ARE ASSOCIATED WITH ONE TABLE BUT THE RESULTS WILL LIKELY AFFECT ONE OR MORE OTHER TABLES.

  3. USING THE STUDENTS TABLE • If a new record is “inserted” into the students table it creating new values for each field in the new record. • If a record is “deleted” in the students table it will lose old values for each field in the new record. • If an “update” command is used to change a record in the students table it will losing values for each field in the record and create new values for each field in the record.

  4. THE 6 BASIC FORMS • It can occur before you insert a set of values into the table. • It can occur after you insert a set of values into the table. • It can occur before you update a record in the table. • It can occur after you update a record in the table. • It can occur before you delete a record in the table. • It can occur after you delete a record in the table.

  5. CREATE A TRIGGER IN GUI

  6. In line 5 you have a choice of: BEFORE/AFTER INSERT/UPDATE/DELETE • In this case choose “after” and “insert”. • In line 7 we will create our queries. Each query ends with a semi-colon. MySQL thinks that a semi-colon is a command to run the query immediately. This is not what we want at the creation stage. The semi-colon at the end of a query is called a “DELIMITER”. • Line 1 changes the delimiter to some other character or series of characters. • In line 7 enter:Insert into FreshStudents (code,firstname,lastname) values (new.code,new.firstname,new.lastname); • Notice that it has a semi-colon at the end but the semi-colon is not the delimiter now. Later we will change it back to a semi-colon so that the query will run when the trigger is activated.

  7. Notice the delimiter at the completion of line 9. This process creates a trigger with one or more queries inside each of which end in a semi-colon. • The last step is line 11 which puts the delimiter back to a semicolon after this process runs.

  8. Highlight all 11 lines and run these by clicking on the blue arrows.

  9. IN MYSQL • mysql> insert into students(studnum,lastname,firstname) values (98763,"Bush","George"); • Query OK, 1 row affected (0.05 sec) • mysql> select * from freshstudents; +--------+----------+-----------+---------+-------+------+ | StudNum | LastName | FirstName | Address | Phone | City | +--------+----------+-----------+---------+-------+------+ | 98763 | Bush | George | NULL | NULL | NULL | +--------+----------+-----------+---------+-------+------+ • 1 row in set (0.00 sec) • Inserting George Bush into Students triggered an insert into “freshstudents”.