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 Update 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 Update Trigger

      Triggers in MySQL are of two types: Before Triggers and After Triggers for various SQL operations like insertion, deletion and update. As we have already learned in previous chapters, the After Update Trigger is executed immediately after a value is updated in a row of a database table. Here, let us learn more about BEFORE UPDATE trigger.

      MySQL Before Update Trigger

      The Before Update Trigger is a row-level trigger supported by the MySQL database. It is type of special stored procedure which is executed automatically before a value is updated in a row of 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 UPDATE statement is executed in the database, the trigger is set to go off first followed by the updated value.

      Syntax

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

      Example

      Let us first create a table named USERS containing the details of users of an application. Use the following CREATE TABLE query to do so −
      CREATE TABLE USERS(
         ID INT AUTO_INCREMENT,
         NAME VARCHAR(100) NOT NULL,
         AGE INT NOT NULL,
         birthDATE VARCHAR(100),
         PRIMARY KEY(ID)
      );
      
      Insert values into the USERS table using the regular INSERT statement as shown below −
      INSERT INTO USERS (Name, Age, birthDATE) VALUES 
      ('Sasha', 23, '24/06/1999');
      ('Alex', 21, '12/01/2001');
      
      The USERS table is created as follows −
      ID
      Name
      Age
      birthDATE
      1
      Sasha
      23
      24/06/1999
      2
      Alex
      21
      12/01/2001
      Creating the trigger:
      Using the following CREATE TRIGGER statement, create a new trigger 'before_update_trigger' on the USERS table to display a customized error using SQLSTATE as follows −
      DELIMITER //
      CREATE TRIGGER before_update_trigger 
      BEFORE UPDATE ON USERS FOR EACH ROW
      BEGIN
         IF NEW.AGE < 0 
         THEN SIGNAL SQLSTATE '45000' 
         SET MESSAGE_TEXT = 'Age Cannot be Negative';
      END IF;
      END //
      DELIMITER ;
      
      Update values of the USERS table using the regular UPDATE statement −
      UPDATE USERS SET AGE = -1 WHERE NAME = 'Sasha';
      

      Output

      An error is displayed as the output for this query −
      ERROR 1644 (45000): Age Cannot be Negative
      

      Before Update Trigger Using a Client Program

      We can also execute the Before Update Trigger using a client program instead of SQL queries directly.

      Syntax

      PHPNodeJSJavaPython
      To execute the Before Update Trigger through a PHP program, we need to execute the CREATE TRIGGER statement using the mysqli function query() as follows −
      $sql = "CREATE TRIGGER before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW
      BEGIN
      IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
      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 before_update_trigger BEFORE UPDATE ON SAMPLE FOR EACH ROW
      BEGIN
      IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
      END IF;
      END";
      if($mysqli->query($sql)){
         printf("Trigger created successfully...!\n");
      }
      $q = "UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha'";
      $result = $mysqli->query($q);
      if ($result == true) {
         printf("Record updated successfully...!\n");
      }
      if($mysqli->error){
         printf("Error message: " , $mysqli->error);
      }
      $mysqli->close();
      

      Output

      The output obtained is as follows −
      Trigger created successfully...!
      PHP Fatal error:  Uncaught mysqli_sql_exception: Age Cannot be Negative