Lecture notes: Triggers and procedures -------------------------------------- Thomas Padron-McCarthy (Thomas.Padron-McCarthy@tech.oru.se), October 9, 2003 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. @ 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 rows integer; open o_c; L1: loop fetch o_c into oldsal; get diagnostics rows = row_count; if rows = 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 rows = row_count; if rows = 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);