Understanding Database 5 - Advanced SQL Queries (Stored Procedure, Trigger, Views)

Stored Procedure 

: Define your commands to run several queries by calling one procedure. You can use the conditional command, and it allows you to run the queries depending on what you get. 

Figure 1. Example of Stored procedure

The statement starts with DELIMITER / and finishes with DELIMITER ; 

 

Trigger

This allows the DBMS to run some query every time a certain query runs. For instance, if you want to update the record of visiting, you can make a trigger like: "After insert the_hotel_visitor_list" -> "UPDATE Record_visiting_history"

The figure below shows the trigger to update the audit_trail bank balance when every bank balance is being updated. (only if the old balance and the new balance are different).

Figure 2. Example of Trigger

 

Views

The view is a virtual table which shows the result of a predefined query. (So you make a query to present what kind of result, and show it whenever you want by calling it) 

CREATE VIEW visitor_log AS
	SELECT name, date, hotel, city
    FROM Hotel_Visitor_History
    WHERE city='SEOUL'; 

SELECT * FROM visitor_log;