[Procedures|Functions|Triggers] CS  325  – Fall  2016 (MySQL  Chapter  15  &  16)

1

Contents 1  Event  – Condition  – Action  cycle 2  [Procedures|Functions|Triggers]  in  SQL 3  Using  [Procedures|Triggers]  for   Information  Processing 4  Restrictions  on   [Procedures|Functions|Triggers]   2

Event-­‐Condition-­‐Action  (ECA) •Event  occurs  in  databases

• e.g.  addition  of  a  new  row,  deletion  of  a  row

•Conditions  are  checked

• e.g.  Is  batch  complete?  Has  student  passed?

•Actions  are  executed  if  conditions  are  satisfied • e.g.  send  batch  to  supplier,  congratulate  student

3

Doing  Information  Processing • (Event)    Processing  of  database  content,   performed  by  the  DBMS  engine  itself,  not  by  the  application  client • (Condition)  Initiated  by  certain  specified  condition,   depending  on  the  control  structure/type  of  the  trigger   • Execution/firing  of  the  procedure|function|trigger • (Action) Data  actions  performed  by  the  procedure|function|trigger • checked  for  necessary privileges • Functions|Triggers Cannot  contain  transaction  control  statements   (COMMIT,  SAVEPOINT,  ROLLBACK not  allowed)

4

Stored  Procedures • A  stored  procedure  contains  a  sequence  of  SQL  commands  stored  in  the   database  catalog  so  that  it  can  be  invoked  later  by  a  program • Stored  procedures  are  declared  using  the  following  syntax: CREATE  PROCEDURE     (param_spec1,  param_spec2,  …,  param_specn )   BEGIN -­‐-­‐ code  goes  here END; where  each  param_spec is  of  the  form: [in  |  out  |  inout]         • in  mode:  allows  you  to  pass  values  into  the  procedure, • out  mode:   allows  you  to  pass  value  back  from  procedure   to  the  calling   program

Example

If  we  create  a  table  to  track  the  total  salaries  of  each  department  (dno)

Let’s  write  a  procedure  to  update   the  salaries  in  the  deptsal table

Example

Step  1: Change  the  delimiter  (i.e.,  terminating  character)  of   SQL  statement  from  semicolon  (;;)  to  something  else  (e.g.,  //)   So  that  you  can  distinguish  between  the  semicolon  of  the  SQL   statements  in  the  procedure  and  the  terminating  character  of   the  procedure  definition

Example

Step  2: 1. Define  a  procedure  called  updateSalary which  takes  as   input  a  department  number.   2. The  body  of  the  procedure  is  an  SQL  command  to  update   the  totalsalary column  of  the  deptsal table.   3. Terminate  the  procedure  definition  using  the  delimiter  you   had  defined  in  step  1  (//)

Example

Step  3: Change  the  delimiter  back  to  semicolon  (;;)

Example

Step  4: Call  the  procedure  to  update  the  totalsalary for  each  department

Example

Step  5: Show  the  updated  total  salary  in  the  deptsal  table

Stored  Procedures  (in  MySQL) • Use  SHOW  PROCEDURE  STATUS  to  display  the  list  of  stored   procedures  you  have  created

• Use  DROP  PROCEDURE  to  remove  a  stored  procedure

Stored  Procedures  (in  MySQL) • You  can  declare  variables  in  stored  procedures • You  can  use  flow  control  statements conditional  IF-­‐THEN-­‐ELSE  or  loops  such  as  WHILE  and  REPEAT

• MySQL  also  supports  cursors  in  stored  procedures. Used  to  iterate  through  a  set  of  rows  returned  by  a  query   allowing  you  to  process  each  individual  row.

Example  using  Cursors • The  previous  procedure  updated  one  row  in  deptsal table  based  on   input  parameter • How  we  update  all  the  rows  in  deptsal simultaneously? (First,  reset  the  totalsalary in  deptsal to  zero)

Example  using  Cursors Drop  the  old  procedure

Use  cursor  to  iterate  the  rows

Example  using  Cursors Call  the  procedure

Another  Example • Create  a  procedure  to  give  a  raise  to  all  employees

Another  Example

Another  Example

Functions  (Stored  Fns,  User  Defined  Fns) • Functions  are  declared  using  the  following  syntax: FUNCTION   (param_spec1,  …,  param_speck)   RETURNS     BEGIN     -­‐-­‐ code  goes  here END; where  param_spec is: [in  |  out  |  in  out]    

Example

Example

Procedures  vs.  Functions The most general difference between procedures and functions is that they are invoked differently and for different purposes: A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records. A function is invoked within an expression and returns a single value directly to the caller to be used in the expression. You cannot invoke a function with a CALL statement, nor can you invoke a procedure in an expression. (from stackoverflow.com) 23

Procedures  vs.  Functions Use procedures for executing business logic. Invoke procedures with a CALL statement Used functions for computations. Invoke functions within SQL queries

(from stackoverflow.com)

24

Procedures  vs.  Functions Parameters passing differs for procedures and functions: Procedure parameters can be defined as input-only, output-only, or both. This means that a procedure can pass values back to the caller by using output parameters. These values can be accessed in statements that follow the CALL statement. Functions have only input parameters. They return a value, so there must be a RETURNS clause in a function definition to indicate the data type of the return value. Also, there must be at least one RETURN statement within the function body to return a value to the caller. (from stackoverflow.com) 25

Procedures  and  Functions Stored routines (referring to both stored procedures and stored functions) are associated with a particular database, just like tables or views. When you drop a database, any stored routines in the database are also dropped. Stored procedures and functions do not share the same namespace. It is possible to have a procedure and a function with the same name in a database.

(from stackoverflow.com) 26

Database  Triggers  in  SQL • Not  specified  in  SQL-­‐92,  but  standardized  in  SQL3  (SQL1999) • Available  in  most  enterprise  DBMSs  (Oracle,  IBM  DB2,  MS  SQL   server)  and  some  public  domain  DBMSs  (Postgres) • but  not  present  in  smaller  desktop  (Oracle  Lite)  and  public  domain   DBMS  (MySQL)

• Some  vendor  DBMS  permit  native  extensions  to  SQL  for   specifying  the  triggers • e.g.  PL/SQL  in  Oracle,  Transact  SQL  in  MS  SQL  Server

• Some  DBMS  also  allow  use  of  general  purpose  programming   language  instead  of  SQL   • e.g.  C/C++  in  Poet,  Java  in  Oracle,  C#/VB  in  SQL  Server

• Some  DBMS  extend  the  triggers  beyond  tables • for  example  also  to  views  as  in  Oracle 27

Types  of  SQL  Triggers • How  many  times  should  the  trigger  body  execute  when  the   triggering  event  takes  place? • Per  statement: the  trigger  body  executes  once  for  the   triggering  event.  This  is  the  default. • For  each  row: the  trigger  body  executes  once  for  each   row  affected  by  the  triggering  event. • When  is  the  trigger  fired? • Relative  to  the  execution  of  an  SQL  DML  statement   (before  or  after  or  instead  of  it) • Exactly  in  a  situation  depending  on  specific  system   resources  (e.g.  signal  from  the  system  clock,  expiring   timer,  exhausting  memory) 28

Statement  and  Row  Triggers Example  1:  Monitoring  Statement  Events SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'EDUCATION', 'NEW YORK');

Execute  only  once  even  if  multiple  rows  affected Example  2:  Monitoring  Row  Events SQL> UPDATE emp 2 SET sal = sal * 1.1 3 WHERE deptno = 30;

Execute  for  each  row  of  the  table  affected  by  the   event 29

Firing  Sequence  of  Database Triggers  on  a  Single  Row BEFORE  statement  trigger

DEPT  table DEPTNO 10 20 30 40

DNAME ACCOUNTING RESEARCH SALES OPERATIONS

LOC NEW YORK DALLAS CHICAGO BOSTON

BEFORE  row  trigger AFTER  row  trigger

AFTER  statement  trigger

30

Firing  Sequence  of  Database   Triggers  on  Multiple  Rows EMP  table EMPNO

ENAME

BEFORE  statement  trigger DEPTNO

7839

KING

30

7698

BLAKE

30

7788

SMITH

30

BEFORE  row  trigger AFTER  row  trigger BEFORE  row  trigger AFTER  row  trigger BEFORE  row  trigger AFTER  row  trigger AFTER  statement  trigger 31

Syntax  for  creating  triggers  in  SQL • Trigger  name -­‐ unique  within  one  database  schema • Timing -­‐ depends  on  the  order  of  controlled  events  (before  or   after  or  instead  of) • Triggering  event -­‐ event  which  fires  the  trigger  (E) • Filtering  condition -­‐ checked  when  the  triggering  event   occurs  (C) • Target -­‐ table  (or  view)  against  which  the  trigger  is  fired;  they   should  be  both  created  within  the  same  schema • Trigger  Parameters -­‐ parameters  used  to  denote  the  record columns;  preceded  by  colon • :new,  :old for  new  and  old  versions  of  the  values  respectively

• Trigger  action -­‐ SQL  statements,  executed  when  the  trigger   fires;  surrounded  by  Begin ...  End (A) 32

Syntax  for  Creating  Statement  Triggers CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name BEGIN SQL statements; END; The  trigger  body  consisting  of  SQL  statements will  be  executed   only  once according  to  the  prescribed  timing,  when  the  event1   (event2,  event3)  occurs  against  the  monitored  table  in  question   table_name. 33

Example:  Registering  Operations SQL> 2 3 4 5 6 7 8

CREATE TRIGGER increase_salary_trg BEFORE UPDATE OF sal ON emp BEGIN INSERT INTO sal_hist(increased, changedOn) VALUES (‘YES’, SYSDATE); END; /

Trigger  name: Timing: Triggering  event: Target: Trigger  action:

increase_salary_trg BEFORE executing  the  statement UPDATE  of  sal column emp table INSERT  values  INTO  sal_hist table 34

Syntax  for  Creating  Row  Triggers CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name [REFERENCING OLD AS old | NEW AS new] FOR EACH ROW [WHEN condition] BEGIN SQL statements; END The  trigger  body  consisting  of  SQL  statements will  be  executed   once  for  each  row affected  by  event1 (event2,  event3)  in  the   table  named  table_name subject  to  the  additional  condition. 35

Example:  Calculating  Derived  Columns SQL>CREATE OR REPLACE TRIGGER derive_commission_trg 2 BEFORE UPDATE OF sal ON emp 3 FOR EACH ROW 4 WHEN (new.job = 'SALESMAN') 5 BEGIN 6 :new.comm := :old.comm * (:new.sal/:old.sal); 7 END; Note: no colon before new in WHEN 8 /

Trigger  name: Timing: Triggering  event: Filtering  condition:       Target: Trigger  parameters: Trigger  action:

derive_commission_trg BEFORE  executing  the  statement UPDATE  of  sal column job  =  ‘SALESMAN’ emp table old,  new calculate  the  new  commission   to  be  updated

36

Trigger  Execution  order 1.  Execute  all  BEFORE  STATEMENT  triggers 2.  Disable  temporarily  all  integrity  constraints  recorded   against  the  table 3.  Loop  for  each  row  in  the  table • Execute  all  BEFORE  ROW  triggers • Execute  the  SQL  statement  against  the  row  and   perform  integrity  constraint  checking  of  the  data • Execute  all  AFTER  ROW  triggers 4.  Complete  deferred  integrity  constraint  checking   against  the  table 5.  Execute  all  AFTER  STATEMENT  triggers 37

Controlling  Triggers  using  SQL Disable  or  Re-­‐enable  a  database  trigger ALTER TRIGGER trigger_name

DISABLE | ENABLE

Disable  or  Re-­‐enable  all  triggers  for  a  table ALTER TABLE table_name

DISABLE | ENABLE

ALL TRIGGERS

Removing  a  trigger  from  the  database DROP TRIGGER trigger_name

38

Using  Triggers  for  Information  Processing • Auditing  Table  Operations • each  time  a  table  is  accessed    auditing   information   is  recorded  against  it

• Tracking  Record  Value  Changes • each  time  a  record  value  is  changed   the  previous   value  is  recorded

• Protecting  Database  Referential  Integrity:   • if  foreign  key  points  to  changing  records referential  integrity   must  be  maintained  

• Maintenance  of  Semantic  Integrity   • e.g.  when  the  factory  is  closed,  all  employees  should   become  unemployed

• Storing  Derived  Data • e.g.  the  number   of  items  in  the  trolley  should   correspond   to  the  current   session  selection

• Security  Access  Control • e.g.  checking  user  privileges   when  accessing  sensitive  information 39

Auditing  Table  Operations USER_NAME SCOTT SCOTT JONES

TABLE_NAME COLUMN_NAME INS EMP 1 EMP SAL EMP 0

UPD 1 1 0

DEL 1 1

…  continuation MAX_INS 5 5

MAX_UPD 5 5 0

MAX_DEL 5 1

40

Example:  Counting  Statement  Execution Whenever  an  employee  record  is  deleted  from  the  database,   the  counter  in  an  audit  table  registering  the  number  of  deleted   rows  for  the  current  user  in  system  variable  USER  is  incremented. SQL>CREATE OR REPLACE TRIGGER audit_emp 2 AFTER DELETE ON emp 3 FOR EACH ROW 4 BEGIN 5 UPDATE audit_table SET del = del + 1 6 WHERE user_name = USER 7 AND table_name = 'EMP’; 7 END; 8 / 41

Example:  Tracing  Record    Value  Changes USER_NAME EGRAVINA

ID OLD_LAST_NAME NEW_LAST_NAME TIMESTAMP HUTTON 12-SEP-04 7950 NULL

NGREENBE

10-AUG-04 7844 MAGEE

TURNER

…  continuation OLD_TITLE NULL

NEW_TITLE OLD_SALARY NEW_SALARY NULL 3500 ANALYST

CLERK

SALESMAN

1100

1100

42

Example:  Recording  Changes Whenever  some  details  for  an  employee  are  deleted  or  updated,   both  the  previous  and  new  details  are  recorded  in  an  audit  table   to  allow  tracing  the  history  of  changes.   SQL>CREATE OR REPLACE TRIGGER audit_emp_values 2 AFTER DELETE OR UPDATE ON emp 3 FOR EACH ROW 4 BEGIN 5 INSERT INTO audit_emp_values (user_name, 6 timestamp, id, old_last_name, new_last_name, 7 old_title, new_title, old_salary, new_salary) 8 VALUES (USER, SYSDATE, :old.empno, :old.ename, 9 :new.ename, :old.job, :new.job, 10 :old.sal, :new.sal); 11 END; 12 / An  insert  operation  cannot  be  recorded  with  this  trigger  as  old.empno has  no  value.

43

Example:  Protecting  Referential  Integrity Whenever  the  department  number  changes,  all  employee  records   for  this  department  will  automatically  be  changed  as  well,  so  that   the  employees  will  continue  to  work  for  the  same  department.

SQL>CREATE OR REPLACE TRIGGER cascade_updates 2 AFTER UPDATE OF deptno ON dept 3 FOR EACH ROW 4 BEGIN 5 UPDATE emp 6 SET emp.deptno = :new.deptno 7 WHERE emp.deptno = :old.deptno; 8 END 9 / 44

Restrictions  for  Database  Triggers Problem: might  be  impossible  to  determine  certain  values   during  execution  of  a  sequence  of  operations   belonging  to  one  and  the  same  transaction Mutating  tables: contain  rows  which  change  their  values  after   certain  operation  and  which  are  used  again   before  the  current  transaction  commits Preventing  table  mutation:   Triggers  should  not  contain  rows… • which  are  constrained  by  rows  from  other  changing  tables • which  are  updated  and  read  in  one  and  the  same  operation • which  are  updated  and  read  via  other  operations  during  the   same  transaction 45

Example:  Mutating  Table SQL> 2 3 4 5 6 7 8 9 10

CREATE OR REPLACE TRIGGER emp_count AFTER DELETE ON emp FOR EACH ROW DECLARE num INTEGER; BEGIN SELECT COUNT(*) INTO num FROM emp; DBMS_OUTPUT.PUT_LINE(' There are now ' || num || ' employees.'); END; /

SQL> DELETE FROM emp 2 WHERE deptno = 30;

Under the bar is code which triggers cascade_updates in this case. Triggers are not executed directly.

ERROR at line 1: ORA-04091: table CGMA2.EMP is mutating, trigger/ function may not see it 46

Example:  Mutating  Table  (fixed) SQL> 2 3 4 5 6 7 8 9 10

CREATE OR REPLACE TRIGGER emp_count AFTER DELETE ON emp -- FOR EACH ROW DECLARE num INTEGER; BEGIN SELECT COUNT(*) INTO num FROM emp; DBMS_OUTPUT.PUT_LINE(' There are now ' || num || ' employees.'); END; /

SQL> DELETE FROM emp

WHERE

deptno = 30;

There are now 8 employees. 6 rows deleted.

Now the trigger becomes a statement trigger and the EMP table is no longer mutating. 47

Best  Practices Rule  1:

Do  not  change  data in  the  primary  key,  foreign  key,  or  unique  key   columns  of  any  table

Rule  2:

Do  not update  records in  the  same  table  you  read   during  the  same  transaction

Rule  3:

Do  not  aggregate over  the  same  table  you  are  updating

Rule  4:

Do  not  read  data from  a  table  which  is  updated   during  the  same  transaction

Rule  5:

Do  not  use  SQL  DCL  (Data  Control  Language)  statements  in  triggers 48