Stored procedures and Triggers

Question 1

what is a stored procedure

Answer 1

segments of declarative sql statements stored in the MySQL server

Question 2

what is flow control

Answer 2

if case loops and calling other procedures

Question 3

give the advantages of stored procedures (3)

Answer 3

minimises bandwidth (just call rather than write query)

consistency

security

Question 4

give the disadvantages of stored procedures (3)

Answer 4

memory requirements

debugging is near impossible

specialised skillset

Question 5

describe how a stored procedure is created

Answer 5

DELIMITER //
CREATE PROCEDURE procedurename
(IN con CHAR(20))
BEGIN
    …
END //
DELIMITER ;

Question 6

how do you delete a stored procedure

Answer 6

drop

Question 7

what are the 3 parameter nodes

Answer 7

IN
OUT
INOUT

Question 8

describe IN parameters

Answer 8

have access to the values

Question 9

describe OUT parameters

Answer 9

can change the value and pass it back

Question 10

how do we alter a stored procedure

Answer 10

drop and start again

Question 11

how do we create and set variables

Answer 11

DECLARE

SET

Question 12

what is the MySQL cursor

Answer 12

allows you to iterate through a record set within a stored procedure

read only, linear

Question 13

what is a trigger

Answer 13

a stored program that is invoked by an event

Question 14

what events can trigger a trigger

Answer 14

INSERT UPDATE DELETE

Question 15

how do we view all triggers

Answer 15

SHOW

Question 16

describe how a trigger is created

Answer 16

CREATE TRIGGER name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table
      BEGIN
       …
      END;

Question 17

what are the new and old modifiers

Answer 17

we put in front of a variable to describe what we want to look at e.g. NEW.name and OLD.name

Question 18

when would we use a before trigger

Answer 18

to check values before a change is made

Question 19

when would we use an after trigger

Answer 19

to make changes in response to an update or similar

Question 20

how do we daisy chain triggers

Answer 20

by specifying follows or precedes