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
      Create 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.

      CREATE TRIGGER

      Triggers are generally defined as responses to an event. For instance, when we hover a mouse-pointer on a drop-down menu of a website, a various set of options to navigate through this website are then displayed. Here, the hovering of the mouse-pointer is an event while the display of options in the drop-down menu is a result of trigger execution. This concept is also introduced in MySQL.
      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).

      Creating Trigger in MySQL

      You can create a trigger using the CREATE TRIGGER Statement.

      Syntax

      Following is the syntax of the MySQL CREATE TRIGGER Statement.
      CREATE TRIGGER trigger_name
      trigger_time trigger_event
      ON table_name FOR EACH ROW
      BEGIN
      ...
      END;
      
      Where,
      • trigger_name is the name of the trigger you need to create
      • trigger_time is the time of trigger activation
      • trigger_event can be INSERT, UPDATE, or DELETE. This event causes the trigger to be invoked.
      • table_name is the name of the table to which the trigger is associated with.

      Example

      Assume we have created a table with name student as shown below −
      CREATE TABLE STUDENT(
         Name varchar(35), 
         Age INT, 
         Score INT
      );
      
      Following query creates 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 ;
      

      Verification

      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),
      ('Pooja', 21, -9),
      ('Devi', 30, 9);
      
      The STUDENT table created will have the following records −
      Name
      Age
      Score
      Jeevan
      22
      8
      Raghav
      26
      0
      Pooja
      21
      0
      Devi
      30
      9
      As we can see, there are no negative values inserted in the table as they are all replaced with zeroes.

      Creating Trigger Using a Client Program

      We can also Create a trigger using a client program.

      Syntax

      PHPNodeJSJavaPython
      To Create a Trigger through a PHP program, we need to execute the CREATE statement using the mysqli function named query() as follows −
      $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";
      $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.');
      $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...!");
      }
      if($mysqli->error){
         printf("Failed..!" , $mysqli->error);
      }
      $mysqli->close();
      

      Output

      The output obtained is as follows −
      Trigger created successfully...!