MySQL triggers use if: Then SQL ElseIf and then end if; Turn from Brother Wu

MySQL Trigger if then ElseIf else's use

CREATE PROCEDURE DBName.Proc_getgrade(Stu_no varchar(20),Cour_no varchar(10)) BEGINDECLARE Stu_gradeFloat; SelectGradeIntoStu_gradeFromGradewhereStudent_no=Stu_noandCourse_no=Cour_no; IfStu_grade>=90 Then SelectStu_grade,A;ElseIf Stu_grade<90 andStu_grade>=80 Then SelectStu_grade,B;ElseIf Stu_grade<80 andStu_grade>=70 Then select Stu_grade, ' C ' Span class= "pun" >;  ElseIf stu_grade70 and Stu_grade>=< Span class= "lit" >60 then select  Stu_grade, ' D ' ; else select Stu_grade, ' E ' ;end if; end 
 
 Case 
 
             

CREATE TRIGGER CFQ
After INSERT on Hc_alarm
For each ROW
BEGIN
declare is_alarm int;
DECLARE Telephoneone VARCHAR (20); Defined
DECLARE telephonetwo VARCHAR (20);
DECLARE Telephonethree VARCHAR (20);
DECLARE Telephonefour VARCHAR (20);
DECLARE telephonefive VARCHAR (20);
SELECTGwsbbj.is_alarm intoIs_alarm from GWSBBJ, (SELECT A.mac from HC_GW A, hc_std b where b.gwid=a.id and B.id=new.stdid) c//assignment
where New.stdid=gwsbbj.stdid and Gwsbbj.gwid=c.mac;
SELECT K.telephoneone, k.telephonetwo,k.telephonethree,k.telephonefour,k.telephonefive into Telephoneone, Telephonetwo,telephonethree,telephonefour,telephonefive//Multiple assignments
From HC_GW F, hc_std G, Hc_install_image K where g.gwid=f.id and G.id=new.stdid and F.mac=k.gwmac;
If New.triggertype = ' std ' and (new.event = ' 017 ' or new.event = ' 015 ') and is_alarm=0 then//an If with an end if
UPDATE GWSBBJ set Gwsbbj.is_alarm =1 where Gwsbbj.stdid=new.stdid;
If Telephoneone is isn't NULL then
Insert into BDNR (Content,tel) VALUES (CONCAT (' Installed ', New.trigger, ' happened ', New.title), Telephoneone);
END if;
If telephonetwo! = "Then
Insert into BDNR (Content,tel) VALUES (CONCAT (' Installed ', New.trigger, ' happened ', New.title), telephonetwo);
END if;
If telephonethree! = "Then
Insert into BDNR (Content,tel) VALUES (CONCAT (' Installed ', New.trigger, ' happened ', New.title), telephonethree);
END if;
If telephonefour! = "Then
Insert into BDNR (Content,tel) VALUES (CONCAT (' Installed ', New.trigger, ' happened ', New.title), telephonefour);
END if;
If telephonefive! = "Then
Insert into BDNR (Content,tel) VALUES (CONCAT (' Installed ', New.trigger, ' happened ', New.title), telephonefive);
END if;
ElseIf
New.triggertype = ' Std ' and (new.event = ' 017 ' or new.event = ' 015 ') and Is_alarm=1
Then
UPDATE GWSBBJ set gwsbbj.is_alarm=1 where Gwsbbj.stdid=new.stdid;
END if;
END;

if... then{
if...then{}
End If;

if...then{}
End If;
...
}
Elseif...then.
End If;

CREATE TRIGGER < trigger name > --The trigger must have a name, a maximum of 64 characters, and may be appended with a delimiter. It is basically like naming other objects in MySQL.
{before | After} --the trigger has a time setting for execution: it can be set either before or after the event occurs.
{INSERT | UPDATE | DELETE} -the same can be set for triggered events: they can be triggered during an insert, update, or delete operation.
On < table name > -Triggers are part of a table: triggers are activated when an INSERT, update, or delete operation is performed on the table. We cannot schedule two triggers for the same event in the same table.
for each ROW -The execution interval of the trigger: the For each row clause notifies the trigger to perform an action every other row, rather than once for the entire table.
< trigger SQL statement > --The trigger contains the SQL statement you want to trigger: The statement here can be any legitimate statement, including compound statements, but the statements here are constrained by the same limitations as functions.

--You must have considerable privileges to create a trigger (created TRIGGER), which is sufficient if you are already a root user. This is different from the SQL standard.

~ ~ Example ~ ~

Example1:

Create a table Tab1

1234	DROPTABLE IF EXISTS tab1;CREATE TABLEtab1(    tab1_id varchar(11));

Create a table TaB2

1234	DROPTABLE IF EXISTS tab2;CREATE TABLEtab2(    tab2_id varchar(11));

Create Trigger: T_AFTERINSERT_ON_TAB1

Function: Automatically adds records to the TAB2 table after adding TAB1 table records

1234567

drop  trigger  if EXISTS t_afterinsert_on_tab1; create  trigger   t_afterinsert_on_tab1 after  insert   on  tab1 for  each ROW begin       insert  into   tab2 (tab2_id) values end

Test it.

1	INSERTINTOtab1(tab1_id) values(‘0001‘);

Look at the results

12	SELECT* FROM tab1;SELECT * FROMtab2;

Example2:

Create Trigger: T_AFTERDELETE_ON_TAB1

Function: Delete the TAB1 table records automatically deleted the corresponding records in the TAB2 table

1234567

DROPTRIGGER IF EXISTS t_afterdelete_on_tab1;CREATE TRIGGER t_afterdelete_on_tab1AFTER DELETE ON tab1FOR EACH ROWBEGIN      delete from tab2 wheretab2_id=old.tab1_id;END;

Test it.

1	DELETEFROM tab1 WHEREtab1_id=‘0001‘;

Look at the results

12	SELECT* FROM tab1;SELECT * FROMtab2;

MySQL triggers use if: Then SQL ElseIf and then end if; Go from Wu