1 / 10

TRIGGERS

TRIGGERS. 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.

vida
Download Presentation

TRIGGERS

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

  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”.

More Related