Types of triggers in MySQL

I am having little knowledge about the triggers. Triggers are objects associated with tables, which are activated when a particular event occurs relative to that table. But i am not aware of how many types of triggers are present in MySQL. So thought to take some notes from you guys. Please provide some information about the types of triggers in MySQL. Also I want to know about the views that are used in MySQL. I know that I am asking another query, but since both are similar, I thought instead of making new thread, I should post in this one. I am expecting some help from your side soon.

When we define a trigger, we determine which event should be activated (inserting rows, change or cancellation) and if it should be performed before or after this event, we will have the following types of triggers:

• BEFORE INSERT
• BEFORE UPDATE
• BEFORE DELETE
• AFTER INSERT
• AFTER UPDATE
• AFTER DELETE

The trigger will establish an education (or a set of instructions) that will be executed for each row affected by the event. Here is the syntax for creating a trigger:

Code:

---

CREATE
    [DEFINER = {user | CURRENT_USER}]
    TRIGGER type name
    ON table FOR EACH ROW instructions

---

The trigger is associated with a table, but is part of a database, so its name must be unique within the same db.

It is important to note that, referring to insertions or deletions of the lines, does not necessarily mean an INSERT or DELETE, but any transaction from which comes the event concerned. For example, data entry can also be done via a LOAD DATA statement. The instructions to perform the activation of the trigger can be one or more than one. In the latter case we use the syntax for compound statements of the type BEGIN .... END as discussed in previous lessons about stored procedures. Note that only from MySQL 5.0.10 code triggers can contain direct references to tables. The DEFINER clause (introduced in MySQL 5.0.17) specifies whether as creator of the trigger should be considered the current user (default) or other user specified in the form name @ host. This will be the user whose permissions will be checked at the time of the trigger.

Once created, the trigger can be removed using the DROP TRIGGER:

Code:

---

DROP TRIGGER [database.] Name

---

Obviously the name of the database, if omitted, is considered equal to the database. It is important to note that, prior to MySQL 5.0.10, this statement requires the name of the trigger was not qualified by the name of the database, but with that of the table on which he was associated. It follows that, if the upgrade from a previous version 5.0.10, you must drop triggers before upgrading and re-create them afterwards, otherwise the DROP TRIGGER will not work after the upgrade. Let's see a practical example of trigger:

Code:

---

delimiter //
 CREATE TRIGGER BEFORE UPDATE ON account upd_check
 FOR EACH ROW
 BEGIN
        NEW.amount IF <0 THEN
                December NEW.amount = 0;
        ELSEIF NEW.amount> 100 THEN
                December NEW.amount = 100;
        END IF;
 END //
 delimiter;

---

This code is activated before any update on the table an account on each of the lines is controlled by changing the value that is being assigned to the field amount to make sure it is between 0 and 100, otherwise is set within these limits. As you can see, then, through the trigger we can change the value that is being updated on the table.

The NEW qualifier indicates that the name of your column that you are using refers to the new value of the row is being updated. NEW you can use in case of INSERT and UPDATE. Similarly, it is available OLD qualifier which refers to pre-edit, and you can use in case of UPDATE and DELETE. The amendment by the SET statement is only possible values for NEW and only type of trigger BEFORE. To work on the trigger is currently required the SUPER privilege.

Views are commonly regarded as a way to display the data in a database with a different structure from what they actually on the database. One use of views is to grant a user access to a table showing a few columns: These columns will be included in the view, on which the user has access permissions, while the negative ones will be on the table. Other possible applications include the ability to read data from multiple tables simultaneously through JOIN or UNION, or include data that are not physically present on the table as calculated from other data. The views may not have the same name as a table belonging to the same database. Here is the syntax to use for creating a view:

Code:

---

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TempTable}]
    [DEFINER = {user | CURRENT_USER}]
    [SQL SECURITY {DEFINER | INVOKER}]
    VIEW name [(list_columns)]
    AS education_select
    [WITH [CASCADED | LOCAL] CHECK OPTION]

---

The OR REPLACE clause lets you replace a view with the same name may already exist. The DEFINER and SQL SECURITY clauses have the same meaning as already seen, respectively, on the trigger and stored procedures, but are only used as of MySQL 5.0.16.

MySQL Triggers are one of the newer features in MySQL that are helping to make it a viable alternative for large enterprise applications.A trigger is associated with a table and is defined to activate when an INSERT, DELETE, or UPDATE statement for the table executes. A trigger can be set to activate either before or after the triggering statement.