<div class="image-550">
<img src="https://cdn.kahimyang.com/resources-20/mysql-triggers.webp" alt="MySQL Triggers" loading="lazy"/>
</div>
<p>
MySQL supports only row-level triggers, meaning a trigger is activated for each row that is inserted, updated, or deleted. For example, if a table has 100 rows inserted, updated, or deleted, the trigger is automatically invoked 100 times for the 100 rows affected. 
</p>
<h4>
Insert Trigger Example
</h4>
<p>
Below is an INSERT MySQL Trigger named "NEWCUSTOMER" that is executed AFTER a row in table CUSTOMERS is inserted. Use the BEFORE statement instead of AFTER to execute the trigger before a row is inserted to our table.
</p>
<pre>
<code class="language-sql line-numbers normalize-whitespace">
delimiter //
create trigger `NEWCUSTOMER` after insert on `CUSTOMERS`
for each row
begin
 if (select 1 from CUSTOMERSTATS where CUSTOMER_ID=new.CUSTOMER_ID) is null then
    insert into CUSTOMERSTATS (CUSTOMER_ID, TRANSACTIONS_NUM) 
	values (new.CUSTOMER_ID, 0);
 end if;
end;//
delimiter ;
</code>
</pre>


<h4>
Delimiter
</h4>
<p>
The default delimiter in MySQL is a semicolon (;). However, when dealing with more intricate statements or stored procedures, it's often required to switch to a different delimiter to prevent any clashes with semicolons used within the statement. To change the delimiter, you can use the following syntax:
</p>

<pre>
<code class="language-sql line-numbers normalize-whitespace">
delimiter NEW_DELIMITER
</code>
</pre>

<p>
In the aforementioned insert trigger, we have designated the double forward slash (//) as our new delimiter. After altering the delimiter, it's crucial to revert it back to the default semicolon once the compound statement has been run.
</p>
<h4>
The Trigger Body
</h4>
<p>
Our trigger body is compound statement following the <b>for each row</b> statement and enclosed by <b>begin</b> and <b>end</b> statements. It checks if the customer already exist in the CUSTOMERSTATS table and insert a new row if it does not.
</p>
<h4>
new.column_name
</h4>
<p>
In our insert trigger, new.CUSTOMER_ID represents the CUSTOMER_ID value post-insertion. For an INSERT trigger, only NEW.column_name is applicable as there's no preceding row. In a DELETE trigger, only OLD.column_name is valid because there's no subsequent row. For an UPDATE trigger, OLD.column_name is used to denote the columns of a row prior to its update, and NEW.column_name is used to indicate the columns of the row following its update.
</p>
<h4>
Update Trigger Example
</h4>
<pre>
<code class="language-sql line-numbers normalize-whitespace">
delimiter //
create trigger `CUSTOMERUPDATE` after update on `CUSTOMERS`
for each row
begin
  if (new.CUSTOMER_NAME <> old.CUSTOMER_NAME) then
    update CUSTOMERSTATS
    set last_updated = current_timestamp()
    where CUSTOMER_ID = new.CUSTOMER_ID ;
  end if;
end;//
delimiter ;
</code>
</pre>
<p>
In the example above, our update trigger is only activated if CUSTOMER_NAME has changed after the update.
</p>
<p>
Keep in mind that the use of triggers can introduce performance overhead, especially if they involve complex operations or are triggered frequently.
</p>
<p>
Each time an event that activates a trigger occurs (an insert, update, or delete), the trigger logic must execute. This means that for every operation that activates the trigger, the MySQL server has to do additional work to execute the trigger's logic.
</p>
<p>
To learn more, visit the <a href="https://dev.mysql.com/" class="news_link">MySQL</a> documentation pages.
</p>




Using MySQL Triggers for Automated Data Integrity