Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Show Trigger

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

       SHOW TRIGGERS

      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.
      MySQL provides a statement to list out all the existing triggers present in a database. Knowing the trigger information can be useful while creating new triggers, so that a user wouldn't use the same name for multiple triggers.

      Show Triggers in MySQL

      The SHOW TRIGGERS Statement is used in MySQL to display information about all the triggers defined in the current database.

      Syntax

      Following is the syntax of the MySQL SHOW TRIGGERS Statement −
      SHOW TRIGGERS
      [{FROM | IN} db_name]
      [LIKE 'pattern' | WHERE expr]
      

      Example

      In this example, we are creating a table named STUDENT using the query below −
      CREATE TABLE STUDENT(
         Name varchar(35), 
         Age INT, 
         Score INT
      );
      
      Following query creates a trigger sample_trigger which will set the score value to 0 if you enter a negative score value into the table.
      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 ;
      
      Assume we have created another trigger using the AFTER clause −
      DELIMITER //
      CREATE TRIGGER testTrigger
      AFTER UPDATE ON Student
      FOR EACH ROW
      BEGIN
       INSERT INTO Student
       SET action = 'update',
       Name = OLD.Name,
       Age = OLD.age,
       Score = OLD.score;
      END;
      END //
      
      DELIMITER ;
      
      Following query shows the existing triggers in the current database −
      SHOW TRIGGERS \G;
      

      Output

      The list of triggers will be displayed as follows −
      *************************** 1. row ***************************
      Trigger: sample_trigger
      Event: INSERT
      Table: student
      Statement: BEGIN
                 IF NEW.score < 0 THEN SET NEW.score = 0;
                 END IF;
                 END
      Timing: BEFORE
      Created: 2021-05-12 19:08:04.50
      sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
      Definer: root@localhost
      character_set_client: utf8mb4
      collation_connection: utf8mb4_0900_ai_ci
        Database Collation: utf8mb4_0900_ai_ci
      *************************** 2. row ***************************
      Trigger: testTrigger
      Event: UPDATE
      Table: student
      Statement: INSERT INTO Student
                 SET Name = OLD.Name,
                     Age = OLD.age,
                     Score = OLD.score
      Timing: AFTER
      Created: 2021-05-12 19:10:44.49
      sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
      Definer: root@localhost
      character_set_client: utf8mb4
      collation_connection: utf8mb4_0900_ai_ci
      Database Collation: utf8mb4_0900_ai_ci
      2 rows in set (0.00 sec)
      

      With FROM or IN Clause

      You can retrieve the information of triggers from a specific database using the FROM clause.

      Example

      Assume that the current database is named demo. Following query shows the triggers present in the database demo −
      SHOW TRIGGERS FROM demo\G
      
      You can also use the IN clause instead of FROM, to get the same output.
      SHOW TRIGGERS IN demo\G
      

      Output

      The existing triggers present in the demo database −
      *************************** 1. row ***************************
                   Trigger: sample_trigger
                     Event: INSERT
                     Table: student
                 Statement: BEGIN
       IF NEW.score < 0 THEN SET NEW.score = 0;
      END IF;
      END
                    Timing: BEFORE
                   Created: 2023-09-29 11:42:33.58
                  sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                   Definer: root@localhost
      character_set_client: cp850
      collation_connection: cp850_general_ci
        Database Collation: utf8mb4_0900_ai_ci
      *************************** 2. row ***************************
                   Trigger: testTrigger
                     Event: UPDATE
                     Table: student
                 Statement: BEGIN
       INSERT INTO Student
       SET action = 'update',
       Name = OLD.Name,
       Age = OLD.age,
       Score = OLD.score;
      END
                    Timing: AFTER
                   Created: 2023-09-29 11:43:10.27
                  sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                   Definer: root@localhost
      character_set_client: cp850
      collation_connection: cp850_general_ci
        Database Collation: utf8mb4_0900_ai_ci
      2 rows in set (0.00 sec)
      

      With WHERE Clause

      You can use the WHERE clause of the SHOW TRIGGERS statements to retrieve info about the triggers which match the specified condition.

      Example

      Following query retrieves the triggers in the current database whose event is update −
      SHOW TRIGGERS FROM demo WHERE Event = 'UPDATE' \G;
      

      Output

      The required list of triggers is displayed as follows −
      *************************** 1. row ***************************
                   Trigger: testTrigger
                     Event: UPDATE
                     Table: student
                 Statement: BEGIN
       INSERT INTO Student
       SET action = 'update',
       Name = OLD.Name,
       Age = OLD.age,
       Score = OLD.score;
      END
                    Timing: AFTER
                   Created: 2023-09-29 11:43:10.27
                  sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                   Definer: root@localhost
      character_set_client: cp850
      collation_connection: cp850_general_ci
        Database Collation: utf8mb4_0900_ai_ci
      1 row in set (0.00 sec)
      

      Showing Trigger Using Client Program

      We can also Show a trigger using a client program.

      Syntax

      PHPNodeJSJavaPython
      To show a trigger through a PHP program, we need to execute the SHOW TRIGGERS statement using the mysqli function query() as follows −
      $sql = "Show TRIGGER";
      $mysqli->query($sql);
      

      Example

      Following are the programs −
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $db = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      printf('Connected successfully.');
      
      // Create a trigger
      $sql = "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";
      
      if ($mysqli->query($sql)) {
          printf("Trigger created successfully...!");
      } else {
          printf("Trigger creation failed: %s", $mysqli->error);
      }
      
      // Show created trigger details
      $sql = "SHOW TRIGGERS"; 
      $res = $mysqli->query($sql);
      if ($res) {
          while ($row = $res->fetch_assoc()) {
              // Print trigger details
              foreach ($row as $key => $value) {
                  printf("%s: %s", $key, $value);
              }
              printf("");
          }
          $res->free();
      } else {
          printf("Failed to retrieve triggers: %s", $mysqli->error);
      }
      $mysqli->close();
      

      Output

      The output obtained is as follows −
      Connected successfully.
      Trigger created successfully...!
      Trigger: testTrigger
      Event: UPDATE
      Table: student
      Statement: INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score
      Timing: AFTER
      Created: 2023-09-08 12:16:27.54
      sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
      Definer: root@localhost
      character_set_client: utf8mb4
      collation_connection: utf8mb4_0900_ai_ci
      Database Collation: utf8mb4_0900_ai_ci