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

      After Insert Trigger

      A Trigger is simply defined as a response to an event. In MySQL, a trigger is a special stored procedure that resides in the system catalogue, that is executed whenever an event is performed. These events include SQL statements like INSERT, UPDATE and DELETE etc.
      It 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.

      MySQL After Insert Trigger

      The After Insert Trigger is a row-level trigger supported by the MySQL database. As its name suggests, the After Insert Trigger is executed right after a value is 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.
      To elaborate, whenever an INSERT statement is executed in the database, the value is inserted into the table first followed by the trigger execution. Hence, one cannot update a newly inserted row using the AFTER INSERT trigger.

      Syntax

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

      Example

      Let us see an example demonstrating the AFTER INSERT trigger. In here, we are creating a new table named USERS, which contains the details of users of an application, using the following query −
      CREATE TABLE USERS(
         ID INT AUTO_INCREMENT,
         NAME VARCHAR(100) NOT NULL,
         AGE INT NOT NULL,
         BIRTH_DATE VARCHAR(100),
         PRIMARY KEY(ID)
      );
      
      Now, let us create another table 'PUSH_NOTIFICATIONS' that is used to store messages to send as push notifications to the users on their birthdays −
      CREATE TABLE PUSH_NOTIFICATIONS(
         ID INT AUTO_INCREMENT,
         BIRTH_DATE VARCHAR(100),
         NOTIFICATIONS VARCHAR(255) NOT NULL,
         PRIMARY KEY(ID)
      );
      
      Using the following CREATE TRIGGER statement, create a new trigger after_trigger on the USERS table to insert values into the PUSH_NOTIFICATIONS table −
      DELIMITER //
      CREATE TRIGGER after_trigger AFTER INSERT ON USERS FOR EACH ROW
      BEGIN
         IF NEW.BIRTH_DATE IS NOT NULL THEN
         INSERT INTO PUSH_NOTIFICATIONS VALUES
         (new.ID, new.BIRTH_DATE, CONCAT('Happy Birthday, ', NEW.NAME, '!'));
      END IF;
      END //
      DELIMITER ;
      
      Insert values into the USERS table using the regular INSERT statement as shown below −
      INSERT INTO USERS (NAME, AGE, BIRTH_DATE) VALUES 
      ('Sasha', 23, '24/06/1999');
      ('Alex', 21, '12/01/2001');
      

      Verification

      To verify if the trigger has been executed, display the PUSH_NOTIFICATIONS table using the SELECT statement −
      ID
      BIRTH_DATE
      NOTIFICATIONS
      1
      24/06/1999
      Happy Birthday, Sasha!
      2
      12/01/2001
      Happy Birthday, Alex!