MySQL - SHOW CREATE TRIGGER Statement

---

---

MySQL SHOW CREATE TRIGGER Statement

Triggers in MySQL are stored programs similar to procedures. These can be created on a table, schema, view and database that are associated with an event and whenever an event occurs the respective trigger is invoked.

Triggers are, in fact, written to be executed in response to any of the following events −

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

The SHOW CREATE TRIGGER Statement displays the CREATE statement used to create the specified trigger.

Syntax

Following is the syntax of the SHOW CREATE TRIGGER Statement.

SHOW CREATE TRIGGER name

Where, name is the name of the trigger for which you need the create statement.

Example

Assume we have created a table with name student as shown below −

Create table Student(
   Name Varchar(35), 
   age INT, 
   Score INT
);

Let us create a trigger, this will set the score value 0 if you enter a value that is less than 0 as score.

DELIMITER //
Create Trigger sample_trigger BEFORE INSERT ON student FOR EACH ROW
BEGIN
IF NEW.score < 0 THEN SET NEW.score = 0;
  END IF;
  END //
DELIMITER ;

Following query displays the query used to create the above trigger. If you try to insert records in the student table and if you use a value that is less than 0 as age it will be automatically set to 0.

INSERT INTO student values 
('Jeevan', 22, 8),
('Raghav', 26, -3),
('Khaleel', 21, -9),
('Deva', 30, 9);

If you verify the contents of the student table you can observe that no negative values inserted under the column score −

SELECT * from student;

Output

Following is the output of the above query −

Name	age	Score
Jeevan	12	8
Raghav	26	0
Khaleel	21	0
Deva	30	9

Assume we have created a trigger using the after clause as shown below −

CREATE TRIGGER testTrigger
AFTER UPDATE ON Student
FOR EACH ROW
INSERT INTO Student
SET action = 'update',
Name = OLD.Name,
age = OLD.age,
score = OLD.score;

Following query displays the CREATE statement used to create the trigger named testTrigger −

SHOW CREATE TRIGGER testTrigger\G;

Output

The above query produces the output shown below −

************ 1. row ************
               Trigger: testTrigger
              sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
			            NO_ZERO_IN_DATE,NO_ZERO_DATE,
						ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER 
                        `testTrigger` 
                        AFTER UPDATE ON `student` FOR EACH ROW INSERT 
						INTO Student
      SET action = 'update',
      Name = OLD.Name,
      age = OLD.age,
      score = OLD.score
  character_set_client: cp850
  collation_connection: cp850_general_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2023-12-12 16:40:02.96