Specify a conditional execution.

 

Syntax

 

<if statement> ::=

 

<if statement then clause> ::= THEN <SQL statement list>

 

<if statement elseif clause> ::= ELSEIF <search condition> THEN <SQL statement list>

 

<if statement else clause> ::= ELSE <SQL statement list>

 

 

Usage

 

The IF statement is used to specify a conditional execution of one or more SQL statements, and is similar in functionality to conditional IF statements found in traditional programming languages.

 

 

Notes

 

The optional ELSEIF clause is used to specify additional conditions with separate statement blocks, and is a convenient shorthand for nesting multiple IF..THEN statements.



 

Only the statements contained in the first IF or ELSEIF clause with a condition that evaluates to true are executed.



 

An optional ELSE clause can be specified to execute one or more statements if none of the preceding conditions were true.



 

 

Examples

 

1)

The following example shows the IF statement used in the body of a trigger definition:



 


DROP TRIGGER IF EXISTS enrolls_changes;    


CREATE TRIGGER enrolls_changes


AFTER INSERT, DELETE, UPDATE ON enrolls


REFERENCING OLD AS o NEW AS n


BEGIN




IF INSERTING THEN




INSERT INTO enrolls_log ( action, n1, n2, n3, n4, stamp )


VALUES ( 'Insert', n.courseID, n.sectionID, n.studentID, n.grade, CURRENT_TIMESTAMP );




ELSEIF UPDATING THEN




INSERT INTO enrolls_log ( action, o1, n1, o2, n2, o3, n3, o4, n4, stamp )


VALUES ('Update', o.courseID, n.courseID, o.sectionID, n.sectionID,


o.studentID, n.studentID, o.grade, n.grade, CURRENT_TIMESTAMP );




ELSEIF DELETING THEN




INSERT INTO enrolls_log ( action, o1, o2, o3, o4, stamp )


VALUES ( 'Delete', o.courseID, o.sectionID, o.studentID, o.grade, CURRENT_TIMESTAMP );




ELSE




SIGNAL 'An unknown trigger event was fired.';




END IF;




END



 

 


 

Conformance

 

SQL:2003 standard

-

SQL/PSM Feature P002-07 "IF statement"