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
      Before Insert 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.

       Before Insert Trigger

      As we have already learned, a Trigger is defined as a response to an event performed. In MySQL, a trigger is called a special stored procedure as it does not require to be invoked explicitly like other stored procedures. The trigger acts automatically whenever the desired event is fired. These events include executing SQL statements like INSERT, UPDATE and DELETE etc.

      MySQL Before Insert Trigger

      The Before Insert Trigger is a row-level trigger supported by the MySQL database. As its name suggests, this trigger is executed right before a value is being inserted into a database table.
      A row-level trigger is a type of trigger that goes off every time a row is modified. Simply, for every single transaction made in a table (like insertion, deletion, update), one trigger acts automatically.
      Whenever an INSERT statement is queried in the database, this Trigger is automatically executed first and then only the value is inserted into the table.

      Syntax

      Following is the syntax to create the BEFORE INSERT trigger in MySQL −
      CREATE TRIGGER trigger_name
      BEFORE INSERT ON table_name FOR EACH ROW
      BEGIN
         -- trigger body
      END;
      

      Example

      Let us see an example demonstrating the BEFORE INSERT trigger. In here, we are creating a new table STUDENT which contains the details of students in an institution, using the following query −
      CREATE TABLE STUDENT(
         Name varchar(35),
         Age INT,
         Score INT,
         Grade CHAR(10)
      );
      
      Using the following CREATE TRIGGER statement, create a new trigger sample_trigger on the STUDENT table. Here, we are checking the score of each student and assigning them with a suitable grade.
      DELIMITER //
      CREATE TRIGGER sample_trigger 
      BEFORE INSERT ON STUDENT FOR EACH ROW
      BEGIN
      IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
      ELSE SET NEW.Grade = 'PASS';
      END IF;
      END //
      DELIMITER ;
      
      Insert values into the STUDENT table using the regular INSERT statement as shown below −
      INSERT INTO STUDENT VALUES
      ('John', 21, 76, NULL),
      ('Jane', 20, 24, NULL),
      ('Rob', 21, 57, NULL),
      ('Albert', 19, 87, NULL);
      

      Verification

      To verify if the trigger has been executed, display the STUDENT table using the SELECT statement −
      Name
      Age
      Score
      Grade
      John
      21
      76
      PASS
      Jane
      20
      24
      FAIL
      Rob
      21
      57
      PASS
      Albert
      19
      87
      PASS

      Before Insert Trigger Using a Client Program

      In addition to create or show a trigger, we can also Perform the "Before Insert trigger" statement using a client program.

      Syntax

      PHPNodeJSJavaPython
      To Perform the Before Insert Trigger through a PHP program, we need to execute the CREATE TRIGGER statement using the mysqli function query() as follows −
      $sql = "Create Trigger sample_trigger BEFORE INSERT ON STUDENT"."
      FOR EACH ROW
      BEGIN
      IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
      ELSE SET NEW.Grade = 'PASS';
      END IF;
      END";
      $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 sample_trigger BEFORE INSERT ON STUDENT"."
         FOR EACH ROW
         BEGIN
         IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
         ELSE SET NEW.Grade = 'PASS';
         END IF;
         END";
         if($mysqli->query($sql)){
            printf("Trigger created successfully...!\n");
         }
         $q = "INSERT INTO STUDENT VALUES ('John', 21, 76, NULL)";
         $result = $mysqli->query($q);
         if ($result == true) {
            printf("Record inserted successfully...!\n");
         }
         $q1 = "SELECT * FROM STUDENT";
         if($r = $mysqli->query($q1)){
            printf("Select query executed successfully...!");
            printf("Table records(Verification): \n");
            while($row = $r->fetch_assoc()){
                printf("Name: %s, Age: %d, Score %d, Grade %s",   
               $row["Name"],
               $row["Age"],
               $row["Score"],
               $row["Grade"]);
               printf("\n");  
            }
        }
         if($mysqli->error){
            printf("Failed..!" , $mysqli->error);
         }
         $mysqli->close();
      

      Output

      The output obtained is as follows −
      Trigger created successfully...!
      Record inserted successfully...!
      Select query executed successfully...!Table records(Verification):
      Name: Jane, Age: 20, Score 24, Grade FAIL
      Name: John, Age: 21, Score 76, Grade PASS