More

    An Overview of Triggers in MySQL

    A trigger is a set of commands invoked automatically when an SQL statement changes data on the associated table. Other stored SQL types in MySQL are events, stored procedures, and stored functions.

    WHEN TRIGGERS ARE INVOKED

    Data can be changed via the INSERT, UPDATE, and DELETE statements. A trigger can be defined to occur BEFORE or AFTER the table data is changed:

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

     WHEN TRIGGERS ARE NOT INVOKED

    1. SQL statements that do not use INSERT, UPDATE, or DELETE but change data will not invoke triggers. For example, both the TRUNCATE and DROP TABLE statements remove rows from a table, but will not invoke triggers.
    2. Foreign key cascading actions (ON DELETE CASCADE and ON UPDATE CASCADE) do not invoke triggers in mysqld 6.0 and will be resolved when foreign keys are implemented across all storage keys in future releases.
    3. Views and temporary tables do not support triggers.

     CREATING A TRIGGER

    The CREATE TRIGGER statement creates a trigger and has the following syntax:

    CREATE TRIGGER triggername

    [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tablename

    FOR EACH ROW {statement}

    SQL statements within a trigger are put inside the BEGIN . . . END compound statement syntax.

    When more than one SQL statements are there, use MySQL’s DELIMITER SQL extension to change the client delimiter while defining the trigger. Else, the first semicolon used to end the first SQL statement in the trigger will be interpreted as the end of the trigger definition and will throw error.

    EXAMPLE:

    delimiter |

    CREATE TRIGGER emptrigger

    AFTER INSERT ON employee

    FOR EACH ROW

    BEGIN

    insert into emp values(NEW.empName,NEW.empId);

    update emp set `first name`=”HJK01″ where id=6;

    END;

    |

    delimiter ;

    To test this insert some data into the employee table.

    FINDING ALL TRIGGERS

    To view all the triggers in a database, use the SHOW TRIGGERS command. SHOW TRIGGERS LIKE ’customer’\G.

    You can also query the INFORMATION_SCHEMA.TRIGGERS table as:

    Select * from INFORMATION_SCHEMA.TRIGGERS

    Or to get all trigger names:

    select TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS;

    The SHOW CREATE TRIGGER command shows the original SQL command used to create the trigger.

    DROPPING A TRIGGER

    To drop a trigger, use the DROP TRIGGER statement. An error is thrown if the trigger does not exist, but error can be prevented by using the IF EXISTS phrase:

    DROP TRIGGER staff_update_date;

    DROP TRIGGER IF EXISTS staff_update_date;

    TRIGGER STORAGE AND BACKUP

    Triggers are stored in the data directory (datadir) in files named tblname.TRG and triggername.TRN. The .TRG files map triggers to tables, and the .TRN files contain the trigger definition. There will be one .TRG file for each table that is affected. If we create six different triggers on four tables, there will be only four .TRG files. When a table is renamed, the trigger files are renamed too, thus no triggers are lost during a table rename. Triggers can be backed up using mysqldump and by copying the .TRG and .TRN files.

    IMPORTANT ADDITIONAL POINTS ABOUT MYSQL TRIGGERS

    1. MySQL does not have a command to change an existing trigger. The only way to change a trigger is to drop the trigger and re-create it. 
    2. Triggers defined by one user in one environment may be invoked by an action from another user in another environment.
    3. By default, a trigger is saved with the sql_mode of the user defining the trigger and can lead to problems if the execution environment has a very different sql_mode.
      • SHOW CREATE TRIGGER or SHOW TRIGGERS can be used to see the sql_mode associated with a trigger. To change the sql_mode of a trigger you have to drop the trigger set the desired sql_mode, re-create the trigger.
    4. A trigger is saved with the current character set and collation stored in the character_set_client and collation_connection system variables. Changing these values in a trigger involves dropping the trigger, setting the character set and collation, and re-creating the trigger.
    5. By default, the trigger is invoked as the user who defined the trigger. However, the trigger definer can be set by using a DEFINER clause in the CREATE TRIGGER statement and a SUPER user can change the DEFINER to any valid user later. If the user does not exist in the grant tables, a warning will be issued.
    6. CREATE TRIGGER and DROP TRIGGER statements are not replicated; hence the trigger must be defined on each database instance.
    7. Triggers cannot modify a table already being used by the statement that invoked the trigger without using the NEW and OLD aliases.
    8. Triggers cannot use dynamic SQL and also many other commands such as SHOW, LOAD, BACKUP DATABASE, RESTORE, COMMIT, ROLLBACK and prepared statement related such as PREPARE, EXECUTE, and DEALLOCATE PREPARE.

    Recent Articles

    OAUTH – FREQUENTLY ASKED QUESTIONS FOR INTERVIEWS AND SELF EVALUATION

    Why is refresh token needed when you have access token? Access tokens are usually short-lived and refresh tokens are...

    SUMO LOGIC VIDEOS AND TUTORIALS

    Sumo Logic Basics - Part 1 of 2 (link is external) (Sep 29, 2016)Sumo Logic Basics - Part 2 of 2...

    GIT – USEFUL COMMANDS

    Discard all local changes, but save them for possible re-use later:  git stash Discarding local changes...

    DISTRIBUTED COMPUTING – RECORDED LECTURES (BITS)

    Module 1 - INTRODUCTION Recorded Lecture - 1.1 Introduction Part I – Definition

    BOOK REVIEW GUIDELINES FOR COOKBOOKS

    Whenever you add reviews for the book, please follow below rules. Write issues in an excel.Create an excel...

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox