1
17/4/20
Practical Database Concepts
Lecture 8: SQL Continued Santha Sumanasekara April 2020
Overview of the lecture
vTriggers in SQL
2
1
3
17/4/20
Triggers
Triggers
Ø Triggers are operations that are automatically performed when a specified database event occurs.
Ø A trigger event can be a changing data action — INSERT, UPDATE, or DELETE.
Ø A trigger action can be another action (SELECT, INSERT, UPDATE, DELETE) action.
4
2
5
17/4/20
Triggers — Examples
Ø When updating a table add a log record to another table (say, action_log) to store timestamp and old and new values.
Ø When inserting a new record into Sales table, updating Inventory table.
Ø When a user view an item on an E-Commerce website, update a User_Recommender table for targeted marketing.
Ø User wants to update a view. A trigger will update the underlying tables Instead!
Triggers – Three types
Ø BEFORE triggers
Ø AFTER Triggers
Ø INSTEAD OF triggers
6
3
7
17/4/20
BEFORE Triggers
Ø BEFORE triggers execute trigger actions before the trigger event is executed.
Ø An example is adding a log record before doing the original action.
Ø E.g. When updating a table add a log record to another table (say, action_log) to store timestamp and old and new values.
BEFORE Triggers – An example
Ø Country table has an attribute ‘population’ to store current population of each country.
Ø Population is variable, and time to time we update them.
Ø Whenever we update the current population of a country, we must keep a log record to store the old population and new population and the timestamp
Ø This helps us to keep track of historical changes of population.
8
4
17/4/20
CREATE TRIGGER update_country_log
BEFORE UPDATE OF population ON country
BEGIN
INSERT INTO country_log VALUES
END;
(NEW.code, OLD.population, NEW.population, datetime(‘now’));
Trigger Action
BEFORE Triggers – An example
Trigger Event
9
CREATE TRIGGER update_country_log
BEFORE UPDATE OF population ON country
BEGIN
INSERT INTO country_log VALUES
END;
(NEW.code, OLD.population, NEW.population, datetime(‘now’);
BEFORE Triggers – An example
NEW.code refers to the country code that is being updated
OLD.population refers to the current population value of the corresponding row in the country table.
10
5
17/4/20
BEFORE Triggers – An example
Country
Country_Log
UPDATE country
SET population = 24433300 WHERE code = ‘AUS’;
This UPDATE will fire the “update_country_log” trigger
11
AFTER Triggers
Ø AFTER triggers execute trigger actions after the trigger event is executed.
Ø An example is display before and after values after doing and update action.
Ø Operation of the AFTER trigger is very similar to BEFORE trigger, only the timing is different.
12
6
17/4/20
INSTEAD OF Triggers
Ø INSTEAD OF triggers are mainly used for updating VIEWs.
Ø When a user asks for updating a view, normally it is not permitted. However, you can use a trigger to update the underlying tables instead.
13
INSTEAD OF Triggers – An example
Ø A user wants to update the name of a continent.
UPDATE continent
SET name = ‘AFRICA’ WHERE name = ‘Africa’;
This UPDATE will fail
because continent is a view.
14
7
17/4/20
INSTEAD OF Triggers – An example
Ø Define an INSTEAD OF trigger to do the job.
Trigger action – the action get executed.
create trigger view_update
INSTEAD OF UPDATE OF name ON continent
BEGIN
UPDATE country
SET continent = NEW.name
WHERE continent = OLD.name; END;
only
Trigger event – not get executed at all.
15
INSTEAD OF Triggers – An example
Ø Define an INSTEAD OF trigger to do the job.
create trigger view_update
INSTEAD OF UPDATE OF name ON continent
BEGIN
UPDATE country
SET continent = NEW.name
WHERE continent = OLD.name; END;
16
8
17
17/4/20
CREATE TRIGGER syntax
About use of OLD and NEW
Ø When executing a trigger, the database engine allows you to refer old and new rows the trigger event is dealing with.
Ø They can be used within trigger action.
Ø Depend on the trigger event, we may use either of them or both.
Trigger Event
Available references
INSERT
NEW
UPDATE
NEW, OLD
DELETE
OLD
18
9
19
17/4/20
SQL Exercise:
Summary
What did we cover?
Ø Single table queries
Ø Querying data on multiple tables (various JOINs)
Ø Aggregation (aggregate functions, GROUP BY, HAVING) Ø Sub-queries (IN, EXISTS, sub-selects)
Ø Set operations
Ø Views
Ø Triggers
What didn’t we cover?
Ø Stored procedures
Ø Transaction Management
Ø Embedding SQL within programs (such as PHP, java) Ø Database Engine-specific features
20
10