Getting Started

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.

For instance

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 //

If you like it you can donate :)

share

Comments

Sign in to publish a comment

0 comments

Be the first to comment on this post.