introduction to triggers using sql - aaupeople.cs.aau.dk/~torp/oracle/trigger.pdf · learning...
TRANSCRIPT
Introduction to Triggers using SQL
Kristian Torp
Department of Computer ScienceAalborg University
www.cs.aau.dk/˜[email protected]
November 24, 2011
daisy.aau.dk
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 1 / 63
Outline
1 A Case Study: General Concepts
2 A Case Study: With Code
3 Overview and MotivationUsage
4 Trigger Types
5 Case Study: Instead of Triggers
6 Managing Triggers
7 Summary
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 2 / 63
Learning Outcomes
GoalsUnderstand basic trigger mechanism
Understand pros and cons of triggers
See procedural code in a DBMS
Why?Widely used in database applications
Widely supported in most DBMSs
NoteConcepts presented are generalCode presented is PostgreSQL specific
One is Oracle specific because not supported on PostgreSQL
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 3 / 63
Two Minute Discussion
Usage of TriggersHave you use triggers before?
Have you used triggers for deriving temporal information?
Does your organization have any policies wrt. triggers?
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 4 / 63
Outline
1 A Case Study: General Concepts
2 A Case Study: With Code
3 Overview and MotivationUsage
4 Trigger Types
5 Case Study: Instead of Triggers
6 Managing Triggers
7 Summary
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 5 / 63
Case Study: Handling Football Players
NoteKeep track of the players in all football clubs
Keep a history of where each player has been playing
At Time 10−− AaB buys the p layer Wurtzi n s e r t i n t o p layer values ’ Wurtz ’ , ’AaB ’ ) ;
Player Table Log TableName Club
Wurtz AaB
Name Club start date stop date
Wurtz AaB 10 null
NoteAll values are copied from the table player to the table log
A value is automatically provided for the column start date
A value cannot be provided for the column stop date
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 6 / 63
Case Study: Handling Football Players
NoteKeep track of the players in all football clubs
Keep a history of where each player has been playing
At Time 10−− AaB buys the p layer Wurtzi n s e r t i n t o p layer values ’ Wurtz ’ , ’AaB ’ ) ;
Player Table Log TableName Club
Wurtz AaB
Name Club start date stop date
Wurtz AaB 10 null
NoteAll values are copied from the table player to the table log
A value is automatically provided for the column start date
A value cannot be provided for the column stop date
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 6 / 63
Case Study: Handling Football Players
NoteKeep track of the players in all football clubs
Keep a history of where each player has been playing
At Time 10−− AaB buys the p layer Wurtzi n s e r t i n t o p layer values ’ Wurtz ’ , ’AaB ’ ) ;
Player Table Log TableName Club
Wurtz AaB
Name Club start date stop date
Wurtz AaB 10 null
NoteAll values are copied from the table player to the table log
A value is automatically provided for the column start date
A value cannot be provided for the column stop date
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 6 / 63
Case Study: Handling Football Players
NoteKeep track of the players in all football clubs
Keep a history of where each player has been playing
At Time 10−− AaB buys the p layer Wurtzi n s e r t i n t o p layer values ’ Wurtz ’ , ’AaB ’ ) ;
Player Table Log TableName Club
Wurtz AaB
Name Club start date stop date
Wurtz AaB 10 null
NoteAll values are copied from the table player to the table log
A value is automatically provided for the column start date
A value cannot be provided for the column stop date
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 6 / 63
Case Study: Handling Football Players, cont.
At Time 12−− AaB buys the p layer Cacai n s e r t i n t o p layer values ’Caca ’ , ’AaB ’ ) ;
Player Table Log TableName Club
Wurtz AaBCaca AaB
Name Club start date stop date
Wurtz AaB 10 null
Caca AaB 12 null
NoteSimilar to the first insert
Start date is the current-time, stop date is unknown/not specified/TBD
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 7 / 63
Case Study: Handling Football Players, cont.
At Time 12−− AaB buys the p layer Cacai n s e r t i n t o p layer values ’Caca ’ , ’AaB ’ ) ;
Player Table Log TableName Club
Wurtz AaBCaca AaB
Name Club start date stop date
Wurtz AaB 10 null
Caca AaB 12 null
NoteSimilar to the first insert
Start date is the current-time, stop date is unknown/not specified/TBD
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 7 / 63
Case Study: Handling Football Players, cont.
At Time 12−− AaB buys the p layer Cacai n s e r t i n t o p layer values ’Caca ’ , ’AaB ’ ) ;
Player Table Log TableName Club
Wurtz AaBCaca AaB
Name Club start date stop date
Wurtz AaB 10 null
Caca AaB 12 null
NoteSimilar to the first insert
Start date is the current-time, stop date is unknown/not specified/TBD
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 7 / 63
Case Study: Handling Football Players, cont.
At Time 12−− AaB buys the p layer Cacai n s e r t i n t o p layer values ’Caca ’ , ’AaB ’ ) ;
Player Table Log TableName Club
Wurtz AaBCaca AaB
Name Club start date stop date
Wurtz AaB 10 null
Caca AaB 12 null
NoteSimilar to the first insert
Start date is the current-time, stop date is unknown/not specified/TBD
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 7 / 63
Case Study: Handling Football Players, cont.
At Time 15−− AaB s e l l s Wurtz to FCKupdate p layer set c lub = ’FCK ’ where name = ’ Wurtz ’ ;
Player Table Log Table
Name Club
Wurtz FCKCaca AaB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 null
Wurtz FCK 15 null
NoteThe table player is naturally updated
The column stop date for the first Wurtz row is updated in the log table
A new row is entered into the log table
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 8 / 63
Case Study: Handling Football Players, cont.
At Time 15−− AaB s e l l s Wurtz to FCKupdate p layer set c lub = ’FCK ’ where name = ’ Wurtz ’ ;
Player Table Log Table
Name Club
Wurtz FCKCaca AaB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 null
Wurtz FCK 15 null
NoteThe table player is naturally updated
The column stop date for the first Wurtz row is updated in the log table
A new row is entered into the log table
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 8 / 63
Case Study: Handling Football Players, cont.
At Time 15−− AaB s e l l s Wurtz to FCKupdate p layer set c lub = ’FCK ’ where name = ’ Wurtz ’ ;
Player Table Log Table
Name Club
Wurtz FCKCaca AaB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 null
Wurtz FCK 15 null
NoteThe table player is naturally updated
The column stop date for the first Wurtz row is updated in the log table
A new row is entered into the log table
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 8 / 63
Case Study: Handling Football Players, cont.
At Time 25−− AaB s e l l s Caca to OBupdate p layer set c lub = ’OB ’ where name = ’Caca ’ ;
Player Table Log Table
Name Club
Wurtz FCKCaca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 null
Caca OB 25 null
NoteSimilar to the first update
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 9 / 63
Case Study: Handling Football Players, cont.
At Time 25−− AaB s e l l s Caca to OBupdate p layer set c lub = ’OB ’ where name = ’Caca ’ ;
Player Table Log Table
Name Club
Wurtz FCKCaca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 null
Caca OB 25 null
NoteSimilar to the first update
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 9 / 63
Case Study: Handling Football Players, cont.
At Time 25−− AaB s e l l s Caca to OBupdate p layer set c lub = ’OB ’ where name = ’Caca ’ ;
Player Table Log Table
Name Club
Wurtz FCKCaca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 null
Caca OB 25 null
NoteSimilar to the first update
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 9 / 63
Case Study: Handling Football Players, cont.
At Time 27−− AaB buys Wurtz back from FCKupdate p layer set c lub = ’AaB ’ where name = ’ Wurtz ’ ;
Player Table Log Table
Name Club
Wurtz AaBCaca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 27Caca OB 25 null
Wurtz AaB 27 null
NoteA new Wurtz row is started in the log tableStill, similar to the previous updates
Basically a delete followed by an insert
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 10 / 63
Case Study: Handling Football Players, cont.
At Time 27−− AaB buys Wurtz back from FCKupdate p layer set c lub = ’AaB ’ where name = ’ Wurtz ’ ;
Player Table Log Table
Name Club
Wurtz AaBCaca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 27Caca OB 25 null
Wurtz AaB 27 null
NoteA new Wurtz row is started in the log tableStill, similar to the previous updates
Basically a delete followed by an insert
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 10 / 63
Case Study: Handling Football Players, cont.
At Time 27−− AaB buys Wurtz back from FCKupdate p layer set c lub = ’AaB ’ where name = ’ Wurtz ’ ;
Player Table Log Table
Name Club
Wurtz AaBCaca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 27Caca OB 25 null
Wurtz AaB 27 null
NoteA new Wurtz row is started in the log tableStill, similar to the previous updates
Basically a delete followed by an insert
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 10 / 63
Case Study: Handling Football Players, cont.
At Time 27−− AaB buys Wurtz back from FCKupdate p layer set c lub = ’AaB ’ where name = ’ Wurtz ’ ;
Player Table Log Table
Name Club
Wurtz AaBCaca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 27Caca OB 25 null
Wurtz AaB 27 null
NoteA new Wurtz row is started in the log tableStill, similar to the previous updates
Basically a delete followed by an insert
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 10 / 63
Case Study: Handling Football Players, cont.
At Time 62−− Wurtz r e t i r e s as an a c t i v e f o o t b a l l p layerde le te from p layer name = ’ Wurtz ’ ;
Player Table Log Table
Name Club
Caca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 27Caca OB 25 null
Wurtz AaB 27 62
NoteThe Wurtz row is deleted from the player table
The value column stop date is update for the last Wurtz row
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 11 / 63
Case Study: Handling Football Players, cont.
At Time 62−− Wurtz r e t i r e s as an a c t i v e f o o t b a l l p layerde le te from p layer name = ’ Wurtz ’ ;
Player Table Log Table
Name Club
Caca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 27Caca OB 25 null
Wurtz AaB 27 62
NoteThe Wurtz row is deleted from the player table
The value column stop date is update for the last Wurtz row
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 11 / 63
Case Study: Handling Football Players, cont.
At Time 62−− Wurtz r e t i r e s as an a c t i v e f o o t b a l l p layerde le te from p layer name = ’ Wurtz ’ ;
Player Table Log Table
Name Club
Caca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 27Caca OB 25 null
Wurtz AaB 27 62
NoteThe Wurtz row is deleted from the player table
The value column stop date is update for the last Wurtz row
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 11 / 63
Case Study: Handling Football Players, cont.
At Time 62−− Wurtz r e t i r e s as an a c t i v e f o o t b a l l p layerde le te from p layer name = ’ Wurtz ’ ;
Player Table Log Table
Name Club
Caca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 27Caca OB 25 null
Wurtz AaB 27 62
NoteThe Wurtz row is deleted from the player table
The value column stop date is update for the last Wurtz row
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 11 / 63
Quiz: Handling Football Players
Final TablesPlayer Table Log Table
Name Club
Caca OB
Name Club start date stop date
Wurtz AaB 10 15Caca AaB 12 25Wurtz FCK 15 27Caca OB 25 null
Wurtz AaB 27 62
QuestionsHow to you identify the active players in the log table?
What is the interpretation of the null values in the log table?
What is the primary key of the log table?
In how many clubs can a single player be active at once?
When are rows deleted from the log table?Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 12 / 63
Outline
1 A Case Study: General Concepts
2 A Case Study: With Code
3 Overview and MotivationUsage
4 Trigger Types
5 Case Study: Instead of Triggers
6 Managing Triggers
7 Summary
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 13 / 63
Audit Trail Example
create tab l e p layer (p l a y e r i d i n t pr imary key ,player name varchar (50) not n u l l ,d a t e o f b i r t h date ,c lub varchar (50) not n u l l ) ;
c reate tab l e p l a y e r l o g (p l a y e r i d i n t not n u l l ,player name varchar (50) not n u l l ,d a t e o f b i r t h date ,c lub varchar (50) not n u l l ,s t a r t d a t e date not n u l l ,s top date date ) ;
NoteThe primary keys are not the same in the two tables
The stop date column is nullable
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 14 / 63
Audit Trail Example
create tab l e p layer (p l a y e r i d i n t pr imary key ,player name varchar (50) not n u l l ,d a t e o f b i r t h date ,c lub varchar (50) not n u l l ) ;
c reate tab l e p l a y e r l o g (p l a y e r i d i n t not n u l l ,player name varchar (50) not n u l l ,d a t e o f b i r t h date ,c lub varchar (50) not n u l l ,s t a r t d a t e date not n u l l ,s top date date ) ;
NoteThe primary keys are not the same in the two tables
The stop date column is nullable
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 14 / 63
Insert Trigger
create or rep lace f u n c t i o n f p l a y e r i n s ( ) r e tu rns t r i g g e r as $$begin
i n s e r t i n t o p l a y e r l o g values (new . p l a y e r i d , new . player name ,new . d a t e o f b i r t h , new . club ,cu r ren t da te , n u l l ) ;
r e t u r n new ;end ;$$ language ’ p lpgsq l ’ ;
c reate t r i g g e r p l a y e r i n sa f t e r i n s e r t on p layerf o r each row execute procedure f p l a y e r i n s ( ) ;
NoteThe new syntax
The null is inserted into the stop date column
The current date is ”give me the current date”
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 15 / 63
Insert Trigger
create or rep lace f u n c t i o n f p l a y e r i n s ( ) r e tu rns t r i g g e r as $$begin
i n s e r t i n t o p l a y e r l o g values (new . p l a y e r i d , new . player name ,new . d a t e o f b i r t h , new . club ,cu r ren t da te , n u l l ) ;
r e t u r n new ;end ;$$ language ’ p lpgsq l ’ ;
c reate t r i g g e r p l a y e r i n sa f t e r i n s e r t on p layerf o r each row execute procedure f p l a y e r i n s ( ) ;
NoteThe new syntax
The null is inserted into the stop date column
The current date is ”give me the current date”
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 15 / 63
Insert Trigger
create or rep lace f u n c t i o n f p l a y e r i n s ( ) r e tu rns t r i g g e r as $$begin
i n s e r t i n t o p l a y e r l o g values (new . p l a y e r i d , new . player name ,new . d a t e o f b i r t h , new . club ,cu r ren t da te , n u l l ) ;
r e t u r n new ;end ;$$ language ’ p lpgsq l ’ ;
c reate t r i g g e r p l a y e r i n sa f t e r i n s e r t on p layerf o r each row execute procedure f p l a y e r i n s ( ) ;
NoteThe new syntax
The null is inserted into the stop date column
The current date is ”give me the current date”
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 15 / 63
Let Dreams Come Trough
i n s e r t i n t o p layer values(101 , ’ Wurtz ’ , ’ 1987−03−03 ’ , ’AaB ’ ) ;
i n s e r t i n t o p layer values(102 , ’ Messi ’ , ’ 1988−02−13 ’ , ’AaB ’ ) ;
update p layerset c lub = ’ Barcelona ’where player name = ’ Messi ’ ;
de le te from p layerwhere p l a y e r i d = 101;
NoteThe “clients” cannot see the derived information
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 16 / 63
Delete Trigger
create or rep lace f u n c t i o n f p l a y e r d e l ( ) r e tu rns t r i g g e r as $$begin
update p l a y e r l o gset s top date = cu r ren t da tewhere p l a y e r i d = o ld . p l a y e r i dand s top date i s n u l l ;r e t u r n o ld ;
end ;$$ language ’ p lpgsq l ’ ;
c reate t r i g g e r p l a y e r d e la f t e r de le te on p layerf o r each row execute procedure f p l a y e r d e l ( ) ;
NoteThis trigger is only fired for delete statements
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 17 / 63
Delete Trigger
create or rep lace f u n c t i o n f p l a y e r d e l ( ) r e tu rns t r i g g e r as $$begin
update p l a y e r l o gset s top date = cu r ren t da tewhere p l a y e r i d = o ld . p l a y e r i dand s top date i s n u l l ;r e t u r n o ld ;
end ;$$ language ’ p lpgsq l ’ ;
c reate t r i g g e r p l a y e r d e la f t e r de le te on p layerf o r each row execute procedure f p l a y e r d e l ( ) ;
NoteThis trigger is only fired for delete statements
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 17 / 63
Delete Trigger
create or rep lace f u n c t i o n f p l a y e r d e l ( ) r e tu rns t r i g g e r as $$begin
update p l a y e r l o gset s top date = cu r ren t da tewhere p l a y e r i d = o ld . p l a y e r i dand s top date i s n u l l ;r e t u r n o ld ;
end ;$$ language ’ p lpgsq l ’ ;
c reate t r i g g e r p l a y e r d e la f t e r de le te on p layerf o r each row execute procedure f p l a y e r d e l ( ) ;
NoteThis trigger is only fired for delete statements
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 17 / 63
Update Trigger
create or rep lace f u n c t i o n f p l aye r up d ( ) re tu rns t r i g g e r as $$begin
−− stop o ldupdate p l a y e r l o gset s top date = cu r ren t da tewhere p l a y e r i d = o ld . p l a y e r i dand s top date i s n u l l ;−− s t a r t newi n s e r t i n t o p l a y e r l o g values (
new . p l a y e r i d , new . player name ,new . d a t e o f b i r t h , new . club ,cu r ren t da te , n u l l ) ;
r e t u r n new ;end ;$$ language ’ p lpgsq l ’ ;
c reate t r i g g e r p layer upda f t e r update on p layerf o r each row execute procedure f p l aye r up d ( ) ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 18 / 63
Update Trigger
create or rep lace f u n c t i o n f p l aye r up d ( ) re tu rns t r i g g e r as $$begin
−− stop o ldupdate p l a y e r l o gset s top date = cu r ren t da tewhere p l a y e r i d = o ld . p l a y e r i dand s top date i s n u l l ;−− s t a r t newi n s e r t i n t o p l a y e r l o g values (
new . p l a y e r i d , new . player name ,new . d a t e o f b i r t h , new . club ,cu r ren t da te , n u l l ) ;
r e t u r n new ;end ;$$ language ’ p lpgsq l ’ ;
c reate t r i g g e r p layer upda f t e r update on p layerf o r each row execute procedure f p l aye r up d ( ) ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 18 / 63
Outline
1 A Case Study: General Concepts
2 A Case Study: With Code
3 Overview and MotivationUsage
4 Trigger Types
5 Case Study: Instead of Triggers
6 Managing Triggers
7 Summary
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 19 / 63
Overview
Triggers are executed implicitlyWhen insert , update, or delete statements are executed
Similar to a stored procedure, i.e., codeCan make call-outs to procedural code
Connected to a tableIn some DBMSs also on a view
Triggers are side effectsNormally considered very bad in software engineering
Triggers not part of SQL-92 first introduced in SQL-1999.Many DBMS have supported triggers for much longer therefore limitedstandard compliance
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 20 / 63
Transition Tables
A set of “internal” tables that the DBMS uses to keep track ofmodifications made by a transaction
Four transition tables for each “real” table R during the execution of atransaction Ti
Rinserted Contains the rows inserted into R during Ti
Rdeleted Contains the rows deleted from R during Ti
Rupdatedold Contains the values of updated rows before Ti
Rupdatednew Contains the values of updated rows after Ti
Rnew = Rold \ Rdeleted \ Rupdatedold ∪ Rinserted ∪ Rupdatednew
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 21 / 63
Outline
1 A Case Study: General Concepts
2 A Case Study: With Code
3 Overview and MotivationUsage
4 Trigger Types
5 Case Study: Instead of Triggers
6 Managing Triggers
7 Summary
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 22 / 63
Usage
To generate derived valuesSum of amount of order lines
To create an audit trailThink STASI
To enforce complex business rulesWhen a customer buys goods for more than 300$ then give a 10%discount
To generate statisticsHow often is a table modified
To provide event loggingEach time a new house is inserted in the for sale table notify potentialcustomers
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 23 / 63
Another Example: Compute Derived Values I
User RequirementsWants to store order and order lines
The total amount of an order is derived from order lines
The total amount is often queried
SolutionStore the derived information total amount with the order
Makes it fast to look-up orders based on total amount
Use triggers to automatically derived the total amountConvenient for the customers
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 24 / 63
Another Example: Compute Derived Values II
create tab l e oorder (oo rde r i d i n t pr imary key ,customer name varchar2 (50) not n u l l ,amount number (10 ,2 ) d e f a u l t 0 not n u l l ,
c o n s t r a i n t amount gt zero check ( amount > 0)de fe r rab le i n i t i a l l y immediate
) ;
c reate tab l e o o r d e r l i n e (oo rde r i d i n t not n u l l ,l i n e n o i n t not n u l l check ( l i n e n o > 0) ,dsc varchar2 (50) not n u l l ,q u a n t i t y i n t not n u l l check ( q u a n t i t y > 0) ,p r ice each number (6 ,2 ) not n u l l check ( pr ice each > 0 . 0 ) ,c o n s t r a i n t o l pk pr imary key ( oorder id , l i n e n o ) ,c o n s t r a i n t o l o f k f o r e i g n key ( oo rde r i d )
re ferences oorder ( oo rde r i d )) ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 25 / 63
Another Example: Compute Derived Values III
create or rep lace t r i g g e r set amounta f t e r i n s e r t or update or de le te on o o r d e r l i n e
f o r each rowdec lare
va l number (10 ,2 ) := 0 ;o id i n t ;
begini f i n s e r t i n g then
va l := : new . q u a n t i t y ∗ : new . pr ice each ;o id := : new . oo rde r i d ;
e l s i f updat ing thenva l := : new . q u a n t i t y ∗ : new . pr ice each −
: o ld . q u a n t i t y ∗ : o ld . pr ice each ;o id := : new . oo rde r i d ;
e l s i f d e l e t i n g thenva l := 0 − : o ld . q u a n t i t y ∗ : o ld . pr ice each ;o id := : o ld . oo rde r i d ;
end i f ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 26 / 63
Another Example: Compute Derived Values IV
update oorderset amount = amount + va lwhere oo rde r i d = o id ;
end ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 27 / 63
A Bad Example
create or rep lace t r i g g e r set amount2a f t e r i n s e r t on oorder
f o r each rowdec lare
tmp amount number ( 1 0 , 2 ) ;begin
s e l e c t sum( q u a n t i t y ∗ pr ice each )i n t o tmp amountfrom o o r d e r l i n ewhere oo rde r i d = : new . oo rde r i d ;
update oorderset amount = tmp amountwhere oo rde r i d = : new . oo rde r i d ;
end ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 28 / 63
Mutating Triggers
Insert trigger trigger
trigger
NoteA trigger can make an insert, this new insert may fire a trigger, etc
Cannot have cycles will cause a mutating trigger
Causes a runtime error
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 29 / 63
Mutating Triggers
Insert trigger trigger
trigger
NoteA trigger can make an insert, this new insert may fire a trigger, etc
Cannot have cycles will cause a mutating trigger
Causes a runtime error
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 29 / 63
Correct Usage
Can have multiple triggers on a tableOrder of execution cannot be specified
Can be hard to understand a database schema with many triggersA trigger cannot modify the table that it is associated with
May cause an infinite loop of trigger executions
Not for Integrity ConstraintsDo not use triggers for enforcing referential integrity!
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 30 / 63
Correct Usage
Can have multiple triggers on a tableOrder of execution cannot be specified
Can be hard to understand a database schema with many triggersA trigger cannot modify the table that it is associated with
May cause an infinite loop of trigger executions
Not for Integrity ConstraintsDo not use triggers for enforcing referential integrity!
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 30 / 63
Trigger Parts
Eventon update of <table name> ...
ConditionMust evaluate to true
ActionCode that is executed
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 31 / 63
Two Minute Discussion
Triggers vs. Application CodeWhy not move trigger logic to applications?
Discuss the pros and cons of this
Would you like to have a trigger on select statementsWhen and where could it be useful?
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 32 / 63
Outline
1 A Case Study: General Concepts
2 A Case Study: With Code
3 Overview and MotivationUsage
4 Trigger Types
5 Case Study: Instead of Triggers
6 Managing Triggers
7 Summary
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 33 / 63
Trigger Types I
{row
statement
}×
{beforeafter
}
A total of four trigger typesSome DBMSs support INSTEAD OF triggers
Very powerful, e.g., to make view updateable or hide legacy code
Before triggers can be used for preconditions
After triggers can be used for postconditions
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 34 / 63
Trigger Types II
Statement level triggers alwaysfires
Row trigger fires only if rows aremodified
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 35 / 63
Row Triggers: Setup Table
Row TriggersExecuted once for each row modified by triggering statement
If no rows modified⇒ trigger is not executed
Example (Create Table)
create tab l e x ( i i n t pr imary key ,j i n t not n u l l ) ;
Example (Populate Table)
i n s e r t i n t o x values ( 1 , 1 ) ;i n s e r t i n t o x values ( 2 , 2 ) ;i n s e r t i n t o x values ( 3 , 3 ) ;commit ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 36 / 63
Before Row Trigger
Example (Stored Procedure)
−− update row before t r i g g e rcreate or rep lace f u n c t i o n f x upd be fo re row ( )re tu rns t r i g g e r as $$begin
ra i se no t i ce ’ before row ’ ;r e t u r n new ;
end ;$$ language ’ p lpgsq l ’ ;
Example (Before Row Trigger)
create t r i g g e r x upd before rowbefore update on x
f o r each row execute procedure f x upd be fo re row ( ) ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 37 / 63
After Row Trigger
Example (Stored Procedure)
−− update row a f t e r t r i g g e rcreate or rep lace f u n c t i o n f x u p d a f t e r r o w ( )re tu rns t r i g g e r as $$begin
ra i se no t i ce ’ a f t e r row ’ ;r e t u r n new ;
end ;$$ language ’ p lpgsq l ’ ;
Example (After Row Trigger)
create t r i g g e r x upd a f te r rowa f t e r update on x
f o r each row execute procedure f x u p d a f t e r r o w ( ) ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 38 / 63
Statement TriggersStatement-Level Triggers
Executed once for each triggering statement
Executed even if no rows are modified
Example (Stored Procedure)create or rep lace f u n c t i o n f x u pd b e f o r e s tm t ( )r e tu rns t r i g g e r as $$begin
ra i se no t i ce ’ before stmt ’ ;r e t u r n new ;
end ;$$ language ’ p lpgsq l ’ ;
Example (Before Statement Trigger)create t r i g g e r x upd befo re s tmt
before update on xf o r each statement execute procedure f x up d be fo re s t m t ( ) ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 39 / 63
Statement TriggersStatement-Level Triggers
Executed once for each triggering statement
Executed even if no rows are modified
Example (Stored Procedure)create or rep lace f u n c t i o n f x u pd b e f o r e s tm t ( )r e tu rns t r i g g e r as $$begin
ra i se no t i ce ’ before stmt ’ ;r e t u r n new ;
end ;$$ language ’ p lpgsq l ’ ;
Example (Before Statement Trigger)create t r i g g e r x upd befo re s tmt
before update on xf o r each statement execute procedure f x up d be fo re s t m t ( ) ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 39 / 63
Statement TriggersStatement-Level Triggers
Executed once for each triggering statement
Executed even if no rows are modified
Example (Stored Procedure)create or rep lace f u n c t i o n f x u pd b e f o r e s tm t ( )r e tu rns t r i g g e r as $$begin
ra i se no t i ce ’ before stmt ’ ;r e t u r n new ;
end ;$$ language ’ p lpgsq l ’ ;
Example (Before Statement Trigger)create t r i g g e r x upd befo re s tmt
before update on xf o r each statement execute procedure f x up d be fo re s t m t ( ) ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 39 / 63
After Statement Trigger
Example (Stored Procedure)
create or rep lace f u n c t i o n f x u p d a f t e r s t m t ( )r e tu rns t r i g g e r as $$begin
ra i se no t i ce ’ a f t e r stmt ’ ;r e t u r n new ;
end ;$$ language ’ p lpgsq l ’ ;
Example (After Statement Trigger)
create t r i g g e r x u p d a f t e r s t m ta f t e r update on x
f o r each statement execute procedure f x u p d a f t e r s t m t ( ) ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 40 / 63
After Statement Trigger
Example (Stored Procedure)
create or rep lace f u n c t i o n f x u p d a f t e r s t m t ( )r e tu rns t r i g g e r as $$begin
ra i se no t i ce ’ a f t e r stmt ’ ;r e t u r n new ;
end ;$$ language ’ p lpgsq l ’ ;
Example (After Statement Trigger)
create t r i g g e r x u p d a f t e r s t m ta f t e r update on x
f o r each statement execute procedure f x u p d a f t e r s t m t ( ) ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 40 / 63
Other Types of Triggers
System Event TriggersAt database start up or shut down
When a DBMS fails
User-Event TtriggersOn logon and logoff
When DML statement executed
When DDL statement executed
NoteThese types of triggers are not part of the SQL standard!
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 41 / 63
Outline
1 A Case Study: General Concepts
2 A Case Study: With Code
3 Overview and MotivationUsage
4 Trigger Types
5 Case Study: Instead of Triggers
6 Managing Triggers
7 Summary
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 42 / 63
Introduction
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 43 / 63
Very Strong Concept
Can make all views updateable
Can make modifications easier to understand
Can wrap ugly database design, by nice design
Can make the changes work for multiple programming languages
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 44 / 63
Mini University Schema
Customer RequirementsWants to have a readable access to students status without joins
A student name must have the column name name
A student status must the column name status
Wants to be able to modify a students status using strings
Challengesname is a reserved word
status is also a name of an exiting table
Currently needs to join to get readable status of students
Currently needs to memorize the int values used for status
SolutionsCreate a view
Use instead-of-triggers on view
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 45 / 63
Create the View
Example (The View)
create or rep lace view s tu de n t s t a t us ass e l e c t s tu . s name as ”name” , s ta . dsc as ” s ta tus ”from student stu , s ta tus s tawhere s tu . s t a t i d = sta . s t a t i d
NoteThe name and status columns are in quotes
A simple view statement
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 46 / 63
Insert Function I
create or rep lace f u n c t i o n f i n s s t u d e n t s t a t u s ( )re tu rns t r i g g e r as $$
dec larev s t a t i d s ta tus . s t a t i d%type ;v s i d s tudent . s id%type ;v no numeric ;
begin−− check t h a t the s ta tus column e x i s t s i n the s ta tus tab l ebegin
s e l e c t s ta . s t a t i di n t o v s t a t i dfrom s ta tus s tawhere s ta . dsc = lower (new . ” s ta tus ” ) ;
except ionwhen no data found then
n u l l ; −− Need to ra i se an e r r o r ! ! ! !end ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 47 / 63
Insert Function II
−− f i n d i f there are any students w i th the same names e l e c t count ( s tu . s id )i n t o v nofrom student s tuwhere s tu . s name = new . ”name” ;i f v no > 0 then
n u l l ; −− r a i se an e r r o rend i f ;−− f i n d a new student i ds e l e c t coalesce (max( s tu . s id ) , 0 )i n t o v s i dfrom student s tu ;v s i d := v s i d + 1;i n s e r t i n t o s tudent
values ( v s id , new . ”name” , v s t a t i d ) ;r e t u r n new ;
end ;$$ language p lpgsq l ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 48 / 63
Insert Instead-Of-Trigger
Example (Create Trigger)
create t r i g g e r i n s s t u d e n t s t a t u sins tead of i n s e r t on s tu de n t s t a t usf o r each row execute procedure f i n s s t u d e n t s t a t u s ( ) ;
NoteAll inserts on student status will call stored procedureView is now updateable
Only insert supported
Very few limit on what can be done in stored procedure
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 49 / 63
Delete Functioncreate or rep lace f u n c t i o n f d e l s t u d e n t s t a t u s ( )
re tu rns t r i g g e r as $$dec lare
v s t a t i d s ta tus . s t a t i d%type ;begin
begins e l e c t s ta . s t a t i di n t o v s t a t i dfrom s ta tus s tawhere s ta . dsc = lower ( o ld . ” s ta tus ” ) ;
except ionwhen no data found then
n u l l ; −− Need to ra i se an e r r o r ! ! ! !end ;
de le te from studentwhere s name = old . ”name”and s t a t i d = v s t a t i d ;
r e t u r n new ;end ;$$ language ’ p lpgsq l ’ ;
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 50 / 63
Delete Instead-Of-Trigger
Example (Create Trigger)
create t r i g g e r d e l s t u d e n t s t a t u sins tead of de le te on s tu de n t s t a t usf o r each row execute procedure f d e l s t u d e n t s t a t u s ( ) ;
NoteAll delete on student status will call stored procedure
Similar idea as for the insert instead-of trigger
Trigger for update can be defined in a similar way
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 51 / 63
Use the Updateable View
Example (Update Through View)
−− i n s e r t through the viewi n s e r t i n t o s t ud en t s t a t usvalues ( ’ Curt ’ , ’ a c t i v e ’ ) ;
−− de le te through the viewde le te from s tu de n t s t a t us
where name = ’Ann ’ ;
NoteView now behave (almost) like a table
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 52 / 63
Summary: Instead-Of Triggers
SummaryA very powerful concept
Generally well-supported most DBMSs
Old or ugly design can removed and old application logic retained
NoteInsert, update, and delete can be combined in a single storedprocedure
Should make sense!
Too many triggers makes the database very hard to maintain!
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 53 / 63
Outline
1 A Case Study: General Concepts
2 A Case Study: With Code
3 Overview and MotivationUsage
4 Trigger Types
5 Case Study: Instead of Triggers
6 Managing Triggers
7 Summary
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 54 / 63
Enabling and Disabling Triggers
Example (Disable Triggers)−− d isab le s i n g l e t r i g g e ra l t e r t ab l e x d isab le t r i g g e r x upd a f te r row ;−− d isab le a l l t r i g g e r s on a s i n g l e t ab l ea l t e r t ab l e x d isab le t r i g g e r a l l ;
Example (Enable Triggers)−− enable s i n g l e t r i g g e ra l t e r t ab l e x enable t r i g g e r x upd a f te r row ;−− enable a l l t r i g g e r s on a s i n g l e tab lea l t e r t ab l e x enable t r i g g e r a l l ;
NoteBe careful, know why a trigger is disabled!
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 55 / 63
Enabling and Disabling Triggers
Example (Disable Triggers)−− d isab le s i n g l e t r i g g e ra l t e r t ab l e x d isab le t r i g g e r x upd a f te r row ;−− d isab le a l l t r i g g e r s on a s i n g l e t ab l ea l t e r t ab l e x d isab le t r i g g e r a l l ;
Example (Enable Triggers)−− enable s i n g l e t r i g g e ra l t e r t ab l e x enable t r i g g e r x upd a f te r row ;−− enable a l l t r i g g e r s on a s i n g l e tab lea l t e r t ab l e x enable t r i g g e r a l l ;
NoteBe careful, know why a trigger is disabled!
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 55 / 63
Enabling and Disabling Triggers
Example (Disable Triggers)−− d isab le s i n g l e t r i g g e ra l t e r t ab l e x d isab le t r i g g e r x upd a f te r row ;−− d isab le a l l t r i g g e r s on a s i n g l e t ab l ea l t e r t ab l e x d isab le t r i g g e r a l l ;
Example (Enable Triggers)−− enable s i n g l e t r i g g e ra l t e r t ab l e x enable t r i g g e r x upd a f te r row ;−− enable a l l t r i g g e r s on a s i n g l e tab lea l t e r t ab l e x enable t r i g g e r a l l ;
NoteBe careful, know why a trigger is disabled!
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 55 / 63
Trigger Metadata
Example (Find Information on All Triggers)s e l e c t ∗from informat ion schema . t r i g g e r s ;
NoteCannot see if trigger is enabled/disabled
Example (Find All Disabled Triggers)s e l e c t p .∗from p g t r i g g e r as pwhere p . tgenabled = ’D ’
NoteThis is PostgreSQL specific
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 56 / 63
Trigger Metadata
Example (Find Information on All Triggers)s e l e c t ∗from informat ion schema . t r i g g e r s ;
NoteCannot see if trigger is enabled/disabled
Example (Find All Disabled Triggers)s e l e c t p .∗from p g t r i g g e r as pwhere p . tgenabled = ’D ’
NoteThis is PostgreSQL specific
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 56 / 63
Dropping Triggers
Example (Find Information on All Triggers)−− Drop row t r i g g e r on tab l edrop t r i g g e r x upd a f te r row on x ;
−− Drop instead−of t r i g g e r on viewdrop t r i g g e r i n s s t u d e n t s t a t u s on s t ud en t s t a t us ;
NoteThe triggers are now removed from the database schema!
There is no syntax for dropping all triggers on a table!
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 57 / 63
Summary: Trigger Management
SummaryTriggers can be enabled/disabled
When table/view dropped all triggers automatically dropped
A view cannot always altered therefore triggers on views cannot bealtered
NoteDisabling triggers can lead to hard to find bugs
Many DBMSs have vendor specific extension for triggers
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 58 / 63
Outline
1 A Case Study: General Concepts
2 A Case Study: With Code
3 Overview and MotivationUsage
4 Trigger Types
5 Case Study: Instead of Triggers
6 Managing Triggers
7 Summary
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 59 / 63
Use or Avoid Triggers?
Use TriggersFor any type of logging
Also called auditing
For deriving information
For advanced checking that cannot be implemented using integrityconstraints
For automating tasks associated with modification statements
For simple data replicationTo wrap bad database design
That must be retained of backwards compatibility reasons
Avoid TriggersWhen a declarative statement can be used
Triggers are procedural
When foreign key or check statement can be used
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 60 / 63
Summary
E-C-A rules (event-condition-action)Triggers are called implicitly
Side-effects
{before, after} × {row, statement}
Instead-of triggers interesting for hiding legacy design Very powerfulconcept
Avoid transaction handling in triggers (commit or rollback)Do not overuse the number of triggers
Can make it very hard to find out what a system does
Many vendor specific extensions for trigger functionalityBecause very late before added to the standard
Core idea large overlap between DBMSs
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 61 / 63
Additional Information
PL/pgSQL www.postgres.cz/index.php/PL/pgSQL_(en)How to write the trigger stored procedures
Triggers in MySQL http://forge.mysql.com/wiki/TriggersGood overview, well explained, linear readable document
Exploring SQL Server Triggersmsdn.microsoft.com/en-us/magazine/cc164047.aspx
Good introduction, quite readable
Triggers in Oracle www.java2s.com/Tutorial/Oracle/0560__Trigger/Catalog0560__Trigger.htm
Only code, many details, Oracle specific
NoteCannot find a good general introduction to triggers
Send me an email if you are aware of such an introduction
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 62 / 63
Two-Minute Digesting
QuestionsWhy can you not specify the order in which triggers execute?
Why can triggers not be stand-alone database objects?
Why are triggers part of the transaction?
You have the same logic that must be executed by a trigger andsometimes explicitly what do you do?
Kristian Torp (Aalborg University) Introduction to Triggers using SQL November 24, 2011 63 / 63