Home > Database > Mysql Tutorial > How to create MySQL trigger before insert?

How to create MySQL trigger before insert?

PHPz
Release: 2023-08-29 14:09:06
forward
1025 people have browsed it

How to create MySQL trigger before insert?

For this, the syntax is as follows -

delimiter //
create trigger yourTriggerName before insert on yourTableName
   FOR EACH ROW
   BEGIN

   yourStatement1
   .
   .
   N
END
//
delimiter ;
Copy after login

To understand the above syntax, let us create a table -

mysql> create table DemoTable1919
   (
   Value int
   );
Query OK, 0 rows affected (0.00 sec)
Copy after login

This is created in MySQL before inserting Query for Trigger-

mysql> delimiter //
mysql> create trigger sumis60 before insert on DemoTable1919
   FOR EACH ROW
   BEGIN
   declare custom_message varchar(256);
        IF (SELECT sum(Value) from DemoTable1919) = 60 THEN
           set custom_message= "cannot insert because sum(Value) is greater than 60";
             signal sqlstate '13500'
             set message_text= custom_message;
         END IF;
     END
     //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
Copy after login

Now you can insert some records in the table using insert command and check the working of trigger-

mysql> insert into DemoTable1919 values(10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1919 values(20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1919 values(30);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1919 values(40);
ERROR 1644 (13500): cannot insert because sum(Value) is greater than 60
Copy after login

The above is the detailed content of How to create MySQL trigger before insert?. For more information, please follow other related articles on the PHP Chinese website!

source:tutorialspoint.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template