Lecture notes: Triggers and procedures
--------------------------------------
Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), November 21, 2021
Triggers
--------
An example:
create view salsumview as
select sum(salary) as salsum
from employee;
select * from salsumview;
+----------------------+
| SALSUM |
+----------------------+
| 296688 |
+----------------------+
1 rows returned
Can be too slow (large tables, complicated queries, frequent use).
Materialize the view:
create table salsum (salsum integer);
insert into salsum (salsum) select sum(salary) from employee;
select salsum from salsum;
But now we must keep the table salsum updated,
whenever employee data is changed: insert, delete, or update.
Solution: Triggers. On employee. One for each operation.
(Note: "ECA" rules = "Event Condition Action".)
@
create trigger emp_insert after insert on employee
referencing new table as n
for each statement
begin atomic
declare newsal integer;
select salary into newsal from n;
update salsum set salsum = salsum + newsal;
end
@
@
create trigger emp_delete after delete on employee
referencing old table as o
for each statement
begin atomic
declare oldsal integer;
select salary into oldsal from o;
update salsum set salsum = salsum - oldsal;
end
@
@
create trigger emp_update after update on employee
referencing old table as o new table as n
for each statement
begin atomic
declare oldsal integer;
declare newsal integer;
select salary into oldsal from o;
select salary into newsal from n;
update salsum set salsum = salsum - oldsal + newsal;
end
@
Another example: Keep track of old employees who used to work here!
create table old_employee (
number integer,
name varchar(20),
salary integer,
manager integer,
birthdate integer,
startdate integer,
primary key(number));
@
create trigger emp_copy after delete on employee
referencing old table as o
for each statement
begin atomic
insert into old_employee
select * from o;
end
@
select * from old_employee;
+------------+---------------------+------------+------------+------------+------------+
| NUMBER | NAME | SALARY | MANAGER | BIRTHDATE | STARTDATE |
+------------+---------------------+------------+------------+------------+------------+
+------------+---------------------+------------+------------+------------+------------+
delete from employee where name = 'Ross, Stuart';
delete from employee where name = 'Ross, Stanley';
select * from old_employee;
+------------+---------------------+------------+------------+------------+------------+
| NUMBER | NAME | SALARY | MANAGER | BIRTHDATE | STARTDATE |
+------------+---------------------+------------+------------+------------+------------+
| 10 | Ross, Stanley | 15908 | 199 | 1927 | 1945 |
| 11 | Ross, Stuart | 12067 | 0 | 1931 | 1932 |
+------------+---------------------+------------+------------+------------+------------+
2 rows returned
Note that the salary sum table was updated by the trigger emp_delete:
select * from salsum;
+------------+
| SALSUM |
+------------+
| 268713 |
+------------+
1 rows returned
Yet another example: Don't allow any pay cuts:
@
create trigger apa after update on employee
referencing new table as n
old table as o
for each statement
begin atomic
declare oldsal integer;
declare newsal integer;
declare empnr integer;
select salary into oldsal from o;
select salary into newsal from n;
select number into empnr from n;
if (oldsal > newsal) then
update employee set salary = oldsal + 1
where number = empnr;
end if;
end
@
select * from employee where name = 'Bullock, J.D.';
+------------+---------------------+------------+------------+------------+------------+
| NUMBER | NAME | SALARY | MANAGER | BIRTHDATE | STARTDATE |
+------------+---------------------+------------+------------+------------+------------+
| 199 | Bullock, J.D. | 27000 | 0 | 1920 | 1920 |
+------------+---------------------+------------+------------+------------+------------+
1 rows returned
update employee set salary = 20000 where name = 'Bullock, J.D.';
1 rows affected
select * from employee where name = 'Bullock, J.D.';
+------------+---------------------+------------+------------+------------+------------+
| NUMBER | NAME | SALARY | MANAGER | BIRTHDATE | STARTDATE |
+------------+---------------------+------------+------------+------------+------------+
| 199 | Bullock, J.D. | 27001 | 0 | 1920 | 1920 |
+------------+---------------------+------------+------------+------------+------------+
1 rows returned
Note:
Some of these triggers will fail if you remove/insert/delete more
than one row in each SQL statement.
("select ... into ...." will fail otherwise.)
But emp_copy works.
For more than one row, it gets more complicated.
(Unless your DBMS supports "for each row".)
@
create trigger emp_update after update on employee
referencing old table as o new table as n
for each statement
begin atomic
declare o_c cursor for select salary from o;
declare n_c cursor for select salary from n;
declare oldsal integer;
declare newsal integer;
declare nrrows integer;
open o_c;
L1:
loop
fetch o_c into oldsal;
get diagnostics nrrows = row_count;
if nrrows = 0 then
leave L1;
end if;
update salsum set salsum = salsum - oldsal;
end loop;
open n_c;
L2:
loop
fetch n_c into newsal;
get diagnostics nrrows = row_count;
if nrrows = 0 then
leave L2;
end if;
update salsum set salsum = salsum + newsal;
end loop;
end
@
Unneccessarily complicated. Can be done as follows.
Remember: Use queries instead of loops.
It is almost always simpler, and often much, much faster.
@
create trigger emp_update after update on employee
referencing old table as o new table as n
for each statement
begin atomic
declare oldsal integer;
declare newsal integer;
select sum(salary) into oldsal from o;
select sum(salary) into newsal from n;
update salsum set salsum = salsum - oldsal + newsal;
end
@
To check that the emp_update trigger works,
we set all employee salaries to 1:
update employee set salary = 1;
25 rows affected
select * from salsum;
+------------+
| SALSUM |
+------------+
| 25 |
+------------+
1 rows returned
Procedures
----------
A rather primitive programming language.
(But Mimer does have a debugger!)
Procedures don't return values. Functions return values.
An example of a function:
@
create function square(i integer)
returns integer
contains sql
begin
return i * i;
end
@
Note:
"contains sql" means the function doesn't read or write the database.
"reads sql data" means the function can read, but not write.
"modifies sql data" means the function can read and write.
select name, salary, square(salary) from employee;
+---------------------+------------+------------+
| NAME | SALARY | |
+---------------------+------------+------------+
| Jones, Tim | 12000 | 144000000 |
| Smith, Paul | 6000 | 36000000 |
| Evans, Michael | 5000 | 25000000 |
| Thomas, Tom | 10000 | 100000000 |
| Edwards, Peter | 9000 | 81000000 |
| Collins, Joanne | 7000 | 49000000 |
| James, Mary | 12000 | 144000000 |
| Thompson, Bob | 13000 | 169000000 |
| Williams, Judy | 9000 | 81000000 |
| Smythe, Carol | 9050 | 81902500 |
| Hayes, Evelyn | 10100 | 102010000 |
| Bullock, J.D. | 27001 | 729054001 |
| Bailey, Chas M. | 8377 | 70174129 |
| Schmidt, Herman | 11204 | 125529616 |
| Wallace, Maggie J. | 7880 | 62094400 |
| Choy, Wanda | 11160 | 124545600 |
| Ferro, Tony | 13621 | 185531641 |
| Raveen, Lemont | 11985 | 143640225 |
| Williams, Bruce | 13374 | 178863876 |
| Zugnoni, Arthur A. | 19868 | 394737424 |
| Brunet, Paul C. | 17674 | 312370276 |
| Iwano, Masahiro | 15641 | 244640881 |
| Onstad, Richard | 8779 | 77070841 |
+---------------------+------------+------------+
23 rows returned
A very simple procedure:
@
create procedure foo(i integer)
contains sql
begin
end
@
call foo(3);
Another one:
@
create procedure lower_overpaid()
modifies sql data
begin
declare a integer;
select avg(salary) into a from employee;
update employee set salary = a where salary > a;
end
@
call lower_overpaid();
This particular procedure could have been done with a single SQL query:
update employee set salary = (select avg(salary) from employee)
where salary > (select avg(salary) from employee);