Correct usage of MySQL triggers

Correct usage of MySQL triggers

I. Basic syntax for creating a trigger:
Create trigger name BEFORE | after trigger event ON table name for each row trigger statement
Trigger events include INSERT, UPDATE, and DELETE.

MySQL trigger application case

MySQL automatic update time trigger

Simple MySQL trigger instance

Ii. Correct Case demonstration
If you want to create a trigger directly in an SQL file, the following is an example of creating a complete SQL file (including a trigger:

Use mysql
Drop database if exists myTest;
Create database myTest default charset = gb2312;
Use myTest;
Create table Stud
(
Sno int (8 ),
Sname varchar (64 ),
Ssex varchar (64 ),
Sage int (4 ),
Sdep varchar (128)
);
Create table Scsc
(
Sno int (8 ),
Cno int (4 ),
Grade int (4)
);
Insert into Stud (sno, sname, ssex, sage, sdep) values (2006126001, 'lwj', 'female ', 19, 'com scen ');
Insert into Stud (sno, sname, ssex, sage, sdep) values (2006126002, 'fj ', 'male', 20, 'com scen ');
Insert into Scsc (sno, cno, grade) values (2006126001,1001, 70 );
Insert into Scsc (sno, cno, grade) values (2006126002,1002, 85 );
-- Pay attention to the usage of the mysql trigger below:
-- When it is stored in an SQL statement and created together with the database/table
-- The end character must be redefined to $ (instead of;). Otherwise, an error occurs during creation.
-- The trigger creation process should be placed before the quit, so that
-- This will not cause the failure to execute conventional SQL statements.
Delimiter $
Drop trigger if exists delTrigger $
Create trigger delTrigger after delete on Stud for each row
Begin
Declare oldsno char (10 );
Set oldsno = old. sno;
Delete from Scsc where sno = oldsno;
End $
Drop trigger if exists updateTgr $
Create trigger updateTgr after update on Stud for each row
Begin
Declare newsno char (10 );
Declare oldsno char (10 );
Set newsno = NEW. sno;
Set oldsno = OLD. sno;
If newsno <> oldsno then
Update Scsc set sno = newsno where sno = oldsno;
End if;
End $
Quit

Figure 1 created successfully

Figure 2 update trigger Test

For more details, please continue to read the highlights on the next page:

1 2 Next Page