Summary: A MySQL trigger is a database object that is related to a table. It will be initiated when a characterized activity is executed for the table. The trigger can be executed when you run any MySQL operation on the table. This post will refer to how to create
Creating a MySQL Trigger in the database and its Use
The following steps should be retracted to create a MySQL trigger, to automate the operations on its own.
Initialize the CREATE TRIGGER statement as
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN ... END;
Attributes of CREATE TRIGGER
As it’s a trigger so it necessarily needs a name, event when it should operate, the table on which the operation is to be performed, and also the time i.e. before or after the event.
Trigger Name. it necessarily needs a name for identification and calling in between, trigger name should be chosen conventionally as to easily identifying its work.
For instance, before_employee_delete, after_student_update.
Trigger Event. Events like INSERT, UPDATE or DELETE these operations cause the trigger to get activated and work on a table, a single event can invoke a trigger, for multiple event-based trigger, each different trigger should be made for each one of the events.
Trigger Time. The trigger can start its operation either BEFORE or AFTER the Trigger event which provokes it to get activated.
Trigger Table It is the table particularly on which the trigger needs to work upon it need to be specified in the CREATE TRIGGER statement as ON table name.
The whole body of the trigger is enclosed in the BEGIN …. END delimiters which kept the functions that serve the purpose.
The trigger is automatically invoked when the given condition inside the trigger body gets true.
Created a trigger to automatically set the grade of the student as Fail if the internal marks entered by students satisfied a prior condition (i.e. if any student submitted his/her marks below 33 the grade should automatically set to F.)
mysql> DELIMITER // mysql> Create Trigger after_insert_studentmarks AFTER INSERT ON student_marks FOR EACH ROW BEGIN IF NEW.marks < 33 THEN SET NEW.grade = F; END IF; END //