How To Create Trigger In Mysql PHPMyAdmin

A trigger is database object which is associated with table, The trigger is activated when a particular event occurs for the table.
The trigger must have an unique name and associate with permanent table, trigger does not work with TEMPORARY table or a view.

Theere are following trigger_event values allowed when the trigger will activate:

INSERT: The trigger activates whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements.

UPDATE: The trigger activates whenever a row is modified; for example, through UPDATE statements.

DELETE: The trigger activates whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE.

You can not define multiple trigger based on same event on a table.

Syntax:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

Example: Now we are creating a trigger for tbl_test1 table, the trigger will activated when the any row updated of tbl_test1 table.

delimiter // 
CREATE TRIGGER tbl_test1_trigger AFTER UPDATE ON tbl_test1_trigger FOR EACH ROW 
BEGIN 
INSERT INTO tbl_test(CHID, referenceId, creationDate, createdBy) VALUES ('11', '11' , 'tbl_test1', NEW.updatedBy, sysdate(), 'DB_trigger');
END 

Where parameters are:
tbl_test1_trigger: trigger name
tbl_test1: table name

  • great explanation. thanks

  • Electrode

    where does the trigger data store?

    • trigger script stored in mysql storage files

  • yes,it will export with sql file