/*
Trigger
Trigger Basic Syntax:
Create trigger trigger name [Before/after] [insert/update/delete] on table name
For each row
Begin
Trigger Body
End
*/
Example one (for reference only)
CREATE TRIGGERTrigger NameAfter insert onTable name
For each row
BEGIN
DECLARERecidnoint(11);
DECLARERowNumint(11);
DECLAREMaxtemperanodecimal(15,2);
DECLAREAvgtemperadecimal(15,2);
SetRowNum = (Select Count(*) fromindicate that where date (date) = DATE(New.datetime));
--Judging the basic grammar
if rownum > 0 Then
--These two statements are the assignment of variables
select maxtempera into Maxtemperano from ( Select * from t_data_temperahumidydefine_d where date (date) = date (new.datetime) order by date desc ) As T_data_temperahumidydefine_h limit 1;
Select avg (tempera) into Avgtempera from T_data_temperahumidydefine_h where date(dateTime) =Date( New.datetime);
IF new.tempera > Maxtemperano Then
Set Maxtemperano = New.tempera;
End IF;
--Modify statements
Update t_data_temperahumidydefine_d
set Maxtempera = Maxtemperano, Avgtempera = Avgtempera
where recId = Recidno;
Else
--Execute INSERT statement
End If;
End
MySQL triggers basic usage and individual syntax