Mysql trigger and stored procedure test bitsCN.com
Mysql trigger and stored procedure test
1. to test the trigger and stored procedure, first create a simple table:
Create table 'airuser '(
'Userid' int (11) not null AUTO_INCREMENT,
'Username' varchar (128) not null,
Primary key ('userid ')
) ENGINE = InnoDB default charset = utf8
2. create a record table for the insert operation of the table:
Create table 'airuser _ record '(
'Id' int (11) not null AUTO_INCREMENT,
'Username' varchar (45) default null,
'Edittime' timestamp null default null,
'Edittype' varchar (45) default null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8
3. Compile an insert trigger:
Drop trigger insert_trigger;
Delimiter |
Create trigger insert_trigger before insert on airuser
FOR EACH ROW BEGIN
Insert into airuser_record SET username = NEW. username, edittime = now (), edittype = 'insert ';
END;
|
Show triggers;
4. write a stored procedure for batch insert:
DROP procedure createUsers;
Delimiter |
Create procedure createUsers (IN count int)
Begin
Declare I int;
Set I = 0;
While I
Insert into airuser set username = concat ('User _ ', I );
Set I = I + 1;
End while;
End;
|
Show procedure status;
5. call the stored procedure to verify that the stored procedure is working and that the trigger can be correctly triggered before a record is inserted:
Call createUsers (10 );
6. verify again by inserting a record table:
SELECT * FROM mars_jpa.airuser_record;
BitsCN.com