Stored procedures and Triggers Flashcards
what is a stored procedure
segments of declarative sql statements stored in the MySQL server
what is flow control
if case loops and calling other procedures
give the advantages of stored procedures (3)
minimises bandwidth (just call rather than write query)
consistency
security
give the disadvantages of stored procedures (3)
memory requirements
debugging is near impossible
specialised skillset
describe how a stored procedure is created
DELIMITER //
CREATE PROCEDURE procedurename
(IN con CHAR(20))
BEGIN
…
END //
DELIMITER ;how do you delete a stored procedure
drop
what are the 3 parameter nodes
IN
OUT
INOUT
describe IN parameters
have access to the values
describe OUT parameters
can change the value and pass it back
how do we alter a stored procedure
drop and start again
how do we create and set variables
DECLARE
SET
what is the MySQL cursor
allows you to iterate through a record set within a stored procedure
read only, linear
what is a trigger
a stored program that is invoked by an event
what events can trigger a trigger
INSERT UPDATE DELETE
how do we view all triggers
SHOW
describe how a trigger is created
CREATE TRIGGER name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table
BEGIN
…
END;what are the new and old modifiers
we put in front of a variable to describe what we want to look at e.g. NEW.name and OLD.name
when would we use a before trigger
to check values before a change is made
when would we use an after trigger
to make changes in response to an update or similar
how do we daisy chain triggers
by specifying follows or precedes
