1 / 33

Procedural Extension to SQL using Triggers

Procedural Extension to SQL using Triggers. Content. 1 Limitations of Relational Data Model for performing Information Processing 2 Database Triggers in SQL 3 Using Database Triggers for Information Processing within DBMS 4 Restrictions for Database Triggers.

daw
Télécharger la présentation

Procedural Extension to SQL using Triggers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Procedural Extension to SQL using Triggers

  2. Content 1 Limitations of Relational Data Model for performing Information Processing 2 Database Triggers in SQL 3 Using Database Triggers for Information Processing within DBMS 4 Restrictions for Database Triggers

  3. Limitations of Relational Data Model • Database vs. Information Systems • DBMS manages data regardless of its usage • IS processes information with respect to its usage • Data model vs. system architecture • Data model does not give interpretation in terms of the application domain • e.g. relational model, hierarchical model, set model • IS architecture is developed so, that the data can be interpreted as information about a particular applied domain • e.g. HR information, financial information, sales information

  4. Event Condition Action (ECA) • Event occurs in databases (DML- insert delete update) • e.g. addition of a new row, deletion of a row • Condition is checked (opzional, written in SQL) • e.g. Is batch complete? Has student passed? • Actions are executed if condition is satisfied (SQL primitives) • e.g. send batch to supplier, congratulate student

  5. Extending Information Processing Capabilities of DBMS using Triggers • I trigger refers to tavola target • Processing of database content, performed by the DBMS engine itself, not by the application client • execution of the trigger (Event)- il trigger è attivato • Initiated by certain specified condition, depending on the type of the trigger • firing of the trigger (Condition) – il trigger è valutato • All data actions performed by the trigger execute within the same transaction in which the trigger fires, but in a separate session (Action)- il trigger è eseguito • Triggers are checked for different privileges as necessary for the processed data • Cannot contain transaction control statements (COMMIT, SAVEPOINT, ROLLBACK not allowed)

  6. Database Triggers in SQL • Not specified in SQL-92, but standardized in SQL3 (SQL1999) • Available in most enterprise DBMS (Oracle, IBM DB2, MS SQL server) and some public domain DBMS (Postgres, MySQL) • but not present in smaller desktop (Oracle Lite) • 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

  7. Types of SQL Triggers • L’attivazione di un trigger relazionale ha due livelli di granularità • Per statement (per primitiva): the trigger body executes once for the triggering event. This is the default. • Row-level (di tupla) the trigger body executes once for each row affected by the triggering event. • La valutazione di un trigger può essere immediata • Relative to the execution of an SQL DML statement: • before – immediatamente prima l’evento che li ha attivati • after – immediatamente dopo l’evento che li ha attivati • Instead – in sostituzione dell’evento che li ha attivati • Exactly in a situation depending on specific system resources (e.g. signal from the system clock, expiring timer, exhausting memory) • La valutazione di un trigger può essere differita, ovvero alla fine della transazione (dopo un commit work) • I trigger possono attivarsi l’un l’altro, in tal caso si parla di trigger in cascata

  8. Example 2: Monitoring Row Events SQL> UPDATE emp 2 SET sal = sal * 1.1 3 WHERE deptno = 30; 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 Execute for each row of the table affected by the event

  9. BEFORE statement trigger AFTER statement trigger Firing Sequence of DatabaseTriggers on a Single Row SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'EDUCATION', 'NEW YORK'); DEPT table DEPTNO 10 20 30 40 DNAME ACCOUNTING RESEARCH SALES OPERATIONS LOC NEW YORK DALLAS CHICAGO BOSTON BEFORE row trigger AFTER row trigger

  10. BEFORE statement trigger BEFORE row trigger AFTER row trigger BEFORE row trigger AFTER row trigger BEFORE row trigger AFTER row trigger AFTER statement trigger Firing Sequence of Database Triggers on Multiple Rows SQL> UPDATE emp 2 SET sal = sal * 1.1 3 WHERE deptno = 30; EMP table EMPNO 7839 7698 7788 ENAME KING BLAKE SMITH DEPTNO 30 30 30

  11. Syntax for creating triggers in SQL • In SQL-3 l’attivazione del trigger è sempre immediata • 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 • Si riferisce alla singola tupla nel caso di row-level, alla tavola nel caso di table-level. • Trigger action - SQL statements, executed when the trigger fires; surrounded by Begin ... End (A)

  12. Syntax for Creating Statement Triggers CREATE [OR REPLACE] TRIGGER rigger_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.

  13. Example: Registering Operations SQL> CREATE TRIGGER increase_salary_trg 2 BEFORE UPDATE OF sal 3 ON emp 4 BEGIN 5 INSERT INTO sal_hist(increased, changedOn) 6 VALUES (‘YES’, SYSDATE); 7 END; 8 / Trigger name:increase_salary_trg Timing:BEFORE executing the statement Triggering event: UPDATE of sal column Target: emp table Trigger action: INSERT values INTO sal_hist table

  14. 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. Referencing si riferisce all’introduzione di variabili

  15. 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; 8 / Trigger name:derive_commission_trg Timing:BEFORE executing the statement Triggering event: UPDATE of sal column Filtering condition: job = ‘SALESMAN’ Target: emp table Trigger parameters: old, new Trigger action:calculate the new commission to be updated Note: no (colon : ) before new in WHEN

  16. 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

  17. Comportamento dei trigger • Limitazione dei trigger before: possono modificare valori di variabili new, ma non contenere comandi DML, per cui non possono attivare altri trigger. • I before trigger possono però richiedere la valutazione anticipata della primitiva che li attiva. • Trigger diversi possono fare riferimento allo stesso evento, per cui possono essere ordinati in modo arbitrario • Di solito trigger creati prima hanno priorità maggiore • Di solito I trigger a livello di tupla quando contengono molte primitive vengono eseguiti tupla per tupla in sequenza.

  18. Trigger e integrità referenziale • Si consideri un’azione S di modifica sulla tavola R. Si consideri che S attivi qualche trigger che modifica varibili new. • Se tali variabili presentano vincoli di integrità referenziale il DBMS procederà al ripristino dell’integrità • Potrebbero attivarsi in cascata sia trigger before che after. Tali after trigger possono mutare lo stato del DB aggiungendosi a quelli già attivati da S, I(S) • Alcuni statement S’ in I(S) possono a loro volta attivare altri trigger, lo stato dell’algoritmo relativo ad S viene salvato ed il sistema procede all’esecuzione dei trigger after e before legati ad S’. • Al termine si ripristina lo stato sospeso di S • Se durante l’esecuzione viene sollevata una eccezione o si incorre in un errore, tutte le modifiche eseguite a partire dalla primitiva transazionale che innescano il trigger vengono interrotte.

  19. Caratteristiche evolute delle regole attive • Si possono includere eventi temporali o applicativi • L’attivazione di trigger può dipendere da un insieme di eventi • Valutazione ed esecuzione possono essere detached ovvero distaccate, avvenendo nel contesto di un’altra transazione • I conflitti tra regole attivate dallo stesso evento possono essere risolti da priorità esplicite. • Le regole possono essere raccolte in gruppi.

  20. Proprietà delle regole attive • Un insieme di regole garantisce la terminazione quando, per ogni transazione che scatena l’esecuzione delle regole, tale esecuzione termina producendo uno stato finale • Un insieme di regole garantisce la confluenza quando, per ogni transazione che scatena l’esecuzione delle regole, tale esecuzione termina producendo un unico stato finale, che non dipende dall’ordine di esecuzione • Un insieme di regole garantisce il determinismo delle osservazioni quando, per ogni transazione che scatena l’esecuzione delle regole, tale esecuzione è confluente e tutte le azioni visibili svolte dalle regole sono identiche e prodotte nello stesso ordine. • Uno strumento per verificare la terminazione di un insieme di regole è il grafo di attivazione. • In presenza di un ciclo il grafo di attivazione non termina. R1 R2 R3

  21. Controlling Triggers using SQL • Disable or Re-enable a database trigger • Disable or Re-enable all triggers for a table • Removing a trigger from the database ALTER TRIGGER trigger_nameDISABLE | ENABLE ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS DROP TRIGGER trigger_name

  22. Using Database 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

  23. Auditing Table Operations USER_NAME SCOTT SCOTT JONES TABLE_NAME EMP EMP EMP COLUMN_NAME SAL INS 1 0 UPD 1 1 0 DEL 1 0 … continuation MAX_INS 5 5 MAX_UPD 5 5 0 MAX_DEL 5 0

  24. Example: Counting Statement Execution 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 / 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.

  25. Example: Tracing Record Value Changes USER_NAME EGRAVINA NGREENBE ID 7950 7844 OLD_LAST_NAME NULL MAGEE NEW_LAST_NAME HUTTON TURNER TIMESTAMP 12-SEP-04 10-AUG-04 … continuation OLD_TITLE NULL CLERK OLD_SALARY NULL 1100 NEW_SALARY 3500 1100 NEW_TITLE ANALYST SALESMAN

  26. Example: Recording 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 / 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. An insert operation cannot be recorded with this trigger as old.empno has no value.

  27. Example: Protecting Referential Integrity 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 / 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.

  28. Restrictions for Database Triggers • Problem: 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: • Should not contain rows which are constrained by rows from other changing tables • Should not contain rows which are updated and read in one and the same operation • Should not contain rows which are updated and read via other operations during the same transaction

  29. Example: Mutating Table SQL> CREATE OR REPLACE TRIGGER emp_count 2 AFTER DELETE ON emp 3 FOR EACH ROW 4 DECLARE 5 num INTEGER; 6 BEGIN 7 SELECT COUNT(*) INTO num FROM emp; 8 DBMS_OUTPUT.PUT_LINE(' There are now ' || num || ' employees.'); 9 END; 10 / SQL> DELETE FROM emp 2 WHERE deptno = 30; ERROR at line 1: ORA-04091: table CGMA2.EMP is mutating, trigger/ function may not see it Under the bar is code entered in SQL-PLUS which triggers cascade_updates in this case. Triggers are not executed directly.

  30. Example: Mutating Table (fixed) SQL> CREATE OR REPLACE TRIGGER emp_count 2 AFTER DELETE ON emp 3 -- FOR EACH ROW 4 DECLARE 5 num INTEGER; 6 BEGIN 7 SELECT COUNT(*) INTO num FROM emp; 8 DBMS_OUTPUT.PUT_LINE(' There are now ' || num || ' employees.'); 9 END; 10 / Now the trigger becomes a statement trigger and the emp table is not longer a mutating table. SQL> DELETE FROM emp WHERE deptno = 30; There are now 8 employees. 6 rows deleted.

  31. Rules for Good Practice • 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

  32. Additional Literature • P. Atzeni, S. Ceri, S.Paraboschi and R. Torlone.Database Systems, Chapter 12 “Active Databases”. McGraw-Hill (1999) • Oracle Database Server Documentation. Oracle9i Database Concepts, Chapter 17 “Triggers”. • Oracle Database Server Documentation. Oracle9i Application Developer's Guide – Fundaments, Chapter 15 “Using Triggers”.

  33. MYSQL and Trigger • In MySQL 5.0 CREATE TRIGGER requires the SUPER privilege. • In MySQL 5.5, all triggers are FOR EACH ROW—that is, the trigger is activated for each row that is inserted, updated, or deleted. MySQL 5.5 does not support triggers using FOR EACH STATEMENT. • A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

More Related