db-triggersviews.pdf - Databases Triggers and Views Jianxin...

This preview shows page 1 - 10 out of 39 pages.

Databases - Triggers and Views Jianxin Li School of Computer Science & Software Engineering University of Western Australia Jianxin Li (UWA) Triggers 1 / 38
Triggers Triggers are small procedural programs – stored routines – written in SQL, that are run automatically when specified events (such as rows being added, deleted or updated) occur on a particular table. Triggers can be used: to perform stronger, or more complex and adaptive, integrity checks than are possible with the inbuilt mechanisms, to automatically perform routine operations, such as logging, tallying, or notification that should occur as a consequence of the original operation, to add additional functionality and/or overcome some of the limitations in any particular implementation of SQL. There is a wide variation between the SQL standard and particular implementations, such as MySQL. Jianxin Li (UWA) Triggers 2 / 38
Basic syntax In MySQL, the basic syntax is CREATE TRIGGER name time event ON table FOR EACH ROW BEGIN ... END Here, name , time , event and table are to be replaced with appropriate keywords or values. Jianxin Li (UWA) Triggers 3 / 38
What options? Clearly name and table are replaced with an appropriate name (for the trigger) and the table to which the trigger is attached, while the other two values can take the following values: time Either BEFORE or AFTER event Either INSERT , UPDATE or DELETE (MySQL does not implement INSTEAD OF which is in the SQL standard, but we can simulate this behaviour if necessary.) Jianxin Li (UWA) Triggers 4 / 38
Reading the docs Jianxin Li (UWA) Triggers 5 / 38
What this means SQL watches out for the specified event ( INSERT , UPDATE or DELETE ) on the specified table, and then runs the trigger either BEFORE or AFTER the action. The trigger can do pretty much anything — prevent the event from happening, modify the event, record the event in another table, ensure that the action is legitimate — to ensure that everything in the database is in a consistent state. Jianxin Li (UWA) Triggers 6 / 38
Row-level only The SQL standard specifies two “levels” of trigger Statement-level trigger Trigger runs once for each relevant type of statement , no matter how many rows it affects. Row-level trigger Trigger runs once for each affected row , maybe thousands of times if there are thousands of rows. MySQL documentation shows FOR EACH ROW as compulsory, because MySQL only implements row-level triggers and not statement-level triggers . Jianxin Li (UWA) Triggers 7 / 38
Access to rows In order to take effective action, the trigger needs to know The state of the row before the (proposed) event The state of the row after the (proposed) event, These values are provided to the trigger as two tuples, called NEW and OLD . Jianxin Li (UWA) Triggers 8 / 38
NEW and OLD A tuple called NEW is available for any INSERT or UPDATE trigger This tuple contains the new values that the row will contain after the insertion or update. A tuple called OLD is available for any UPDATE or DELETE trigger This tuple contains the old values that the row did contain after the update or deletion. For an UPDATE

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture