BUSINESS RULES CONTROL
Dato
Application
Code
Data Base
1° Controllo
Procedure
Client Side
3° Controllo
Table
Integrity
Constraint
2° Controllo
Event Driven
Trigger
Code
Pericolo sui Dati
Un R.D.B.M.S. deve proteggere i dati da una svariata serie di insidie.
•
Errori Accidentali (programming errors): Integrity issues.
•
Utilizzo Illecito: Security issue.
•
Hardware e Software Failures: Restart issues.
Types SQL constraints
-)
-)
-)
-)
-)
NOT NULL
UNIQUE
PRIMARY KEY
Referential integrity (FOREIGN KEY)
General assertion (CHECK’s)
Status SQL constraints
-) Enabled
-) Disabled
Using Index – Storage Option
Qualche Caratteristica SQL Constraints
•
•
•
•
•
•
•
•
Migliorano le Performances
Facili da dichiarare / modificare
Centralizzano i controlli
Immediatamente producono un feed back utente
Flessibili (enabled / disabled)
Pienamente documentati nel dizionario dati
Definibili daI SQL Statement CREATE TABLE / ALTER TABLE
Definibili a livello di Tabella o di Colonna
•
•
XXX_CONSTRAINTS
XXX_CONS_COLUMNS
•
•
•
USER – Relativi alle tabelle poste nello schema dello user
con il quale siamo connessi
ALL – Relativi alle tabelle accessibili dall’utente con il
quale siamo connessi
DBA – Relativi a tutte le tabelle
Status SQL Constraints
DISABLED
Sospesi i controlli
Gli indici associati sono rimossi
Stato giustificato da:
--- grosse operazioni batch
--- loader massivi
--- import di oggetti tabellari separate
ENABLED
Con la tabella in “Lock” vengono effettuati
i controlli sui record esistenti
Gli indici associati sono ricreati
Riprendono i controlli sulle nuove DML
Oracle Integrity Constraints: Table
Un TABLE CONSTRAINT è identico, sintatticamente alla stesura che vedremo per
l’azione su colonna con l’unica differenza che può gestire più campi della stessa
tabella.
[CONSTRAINT constraint]
{[NOT] NULL |
[ {UNIQUE | PRIMARY KEY} (column[, column])
[FOREIGN KEY (column[, column])
[REFERENCES [user.] table[ (column[, column]) ] [ON DELETE CASCADE]
[CHECK (condition) ]
Oracle Integrity Constraints: Column
Un integrity constraint applica una politica restrittiva sui valori relativi ad una o più
colonne in una tavola.
Column CONSTRAINT clauses può apparire in
CREATE TABLE
ALTER TABLE
SQL statement.
[CONSTRAINT constraint]
[[NOT] NULL | UNIQUE | PRIMARY KEY ]
[REFERENCES [user.] table[ (column) ] [ON DELETE CASCADE]
[CHECK (condition) ]
UNIQUE Constraints in Oracle
Il constraint di UNIQUE designa una colonna, o una combinazione di esse, ad
assumere, nel caso risultassero valorizzate, valori univoci.
Una “unique key column” non può essere di tipologia
LONG o
LONG RAW.
Non è tecnicamente fattibile designare lo stesso insieme di colonne sia per una
unique key che per una primary key o una cluster key.
E’ possibile designare lo stesso insieme di colonne sia per una “unique key” che per
una “foreign key”.
Examples of Unique in Oracle
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(9) CONSTRAINT unq_dname UNIQUE,
loc VARCHAR2(10) ) ;
In alternativa, è possibile utilizzare la seguente constraint syntax:
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(9),
loc VARCHAR2(10),
CONSTRAINT unq_dname UNIQUE (dname)
USING INDEX TABLESPACE …….
STORAGE (…..)
PCTFREE …
);
PRIMARY KEY Constraints in Oracle
Una PRIMARY KEY caratterizza una colonna, o un insieme di esse, in grado di
individuare il RECORD per tutta la permanenza nella Base Dati.
In sintesi si tratta di un set di colonne i cui valori devono risultare:
 Univoci
 Totali
 Immutabili.
Una table può avere una ed esclusivamente una chiave primaria.
Una “primary key column” non può essere di tipologia:
LONG o
LONG RAW.
Defining Primary Keys in Oracle
CREATE TABLE dept
(deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY,
dname VARCHAR2(9),
loc VARCHAR2(10) )
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(9),
loc VARCHAR2(10),
CONSTRAINT pk_dept PRIMARY KEY (deptno)
USING INDEX TABLESPACE …….
STORAGE (…..)
PCTFREE …
)
FOREIGN KEY Constraints in Oracle
DEPT
DEPT
Dno Dname
D5 Research
D6 Advertising
D7 Newprojects
EMP
EMP
Eno Ename Dno
E1 Smith D5
E2 Black D6
E3 Jones D6
E4 Brown deve essere inserito. Quali check devo considerare al fine di
mantenere l’integrità della base dati?
Un tentativo di “delete” D5 Research occorre. Quali possibili azioni devo
considerare al fine di mantenere l’integrità della base dati?
Maintain Referential Integrity
Event
Delete of Parent
Parent
Child
Action
Delete corresponding Child records
(Cascading Delete)
Set the Foreign Key to null in the
corresponding Child records
(Delete Nullifies)
Do not allow the delete
of the Parent record if any corresponding
Child records exist (Restricted Delete)
Maintain Referential Integrity
Event
Delete of Parent
Action
Update Foreign Key of corresponding
Child records (Cascading Update)
Update of Primary
Key of Parent
Parent
Child
Set the Foreign Key to null in the
corresponding Child records
(Update Nullifies)
Do not allow the update
of the Parent record if any corresponding
Child records exist (Restricted Update)
Maintain Referential Integrity
Event
Action
Delete of Parent
Update of Primary
Key of Parent
Parent
Child
Insert of Child
Check that null or a valid Primary Key from
the Parent has been specified for the Foreign
Key
Oracle Referential Integrity Constraints
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job
VARCHAR2(9),
mgr
NUMBER(4),
hiredate DATE,
sal
NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES
dept(deptno) )
La FOREIGN KEY deve referenziare un insieme di colonne sulle quali agisce
Una PRIMARY KEY oppure un UNIQUE CONSTRAINT
ON DELETE CASCADE Option
CREATE TABLE emp
(empno
NUMBER(4),
ename
VARCHAR2(10),
job
VARCHAR2(9),
mgr
NUMBER(4),
hiredate
DATE,
sal
NUMBER(7,2),
comm
NUMBER(7,2),
deptno
NUMBER(2)
CONSTRAINT fk_deptno
REFERENCES dept(deptno)
ON DELETE CASCADE )
CHECK Constraint on a Column
CREATE TABLE dept
(deptno NUMBER
dname VARCHAR2(9)
loc VARCHAR2(10)
CONSTRAINT check_deptno
CHECK (deptno BETWEEN 10 AND 99)
DISABLE,
CONSTRAINT check_dname
CHECK (dname = UPPER(dname))
DISABLE,
CONSTRAINT check_loc
CHECK (loc IN ('DALLAS','BOSTON',
'NEW YORK','CHICAGO'))
DISABLE)
Example of a CHECK Constraint on a Table
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
job
VARCHAR2(9),
mgr
NUMBER(4),
hiredate DATE,
sal
NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
CHECK (sal + comm <= 5000) )
Triggers contrasted with routines
Procedures are called explicitly
Triggers are event-driven
Database Triggers
•
Centralized actions can be defined using a non declarative
approach (writing PL/SQL code) with database triggers.
•
A database trigger is a stored procedure that is fired (implicitly
executed) when an INSERT, UPDATE, or DELETE statement is
issued against the associated table.
•
Database triggers can be used to customize a database
management system:
– value-based auditing
– automated data generation
– the enforcement of complex security checks
– enforce integrity rules
– enforce complex business rules
TRIGGER STRUCTURE
the SQL statement
that causes a
trigger to be fired
trigger restriction
Regola di
Scatto
Effetto
PL/SQL Code
trigger action
triggering event
the procedure (PL/SQL block) that
contains the SQL statements
and PL/SQL code to be executed when a
triggering statement
is issued and the trigger restriction
evaluates to TRUE.
Insert
After
Before
Update of
Delete
specifies a Boolean expression that must be
TRUE
for the trigger to fire.
The trigger action is not executed if the
trigger restriction
evaluates to FALSE or UNKNOWN
Example : maintaining derived values
Event
CREATE OR REPLACE TRIGGER increment_courses
AFTER INSERT ON enrol
Condition
row trigger
Action
column values for current row
and new/old correlation names
FOR EACH ROW
BEGIN
update students
set numofcourses = numofcourses + 1
where students.studno = :new.studno
END;
Example Integrity Trigger in Oracle
Event
Condition
Action
CREATE TRIGGER labmark_check
BEFORE INSERT OR UPDATE OF labmark ON enrol
DECLARE
bad_value exception;
WHEN (old.labmark IS NOT NULL OR new.labmark IS NOT
NULL)
row trigger
FOR EACH ROW
BEGIN
IF :new.labmark < :old.labmark
THEN raise bad_value ;
END IF;
EXCEPTION
WHEN bad_value THEN
raise_application_error(-20221,‘New labmark
lower
than old labmark’ );
END;
Some Cautionary Notes about Triggers
SQL statement
UPDATE T1 SET …;
Fires the UPDATE-T1 Trigger
•
•
•
Triggers are useful for customizing a database.
But the excessive use of triggers can result in
complex interdependencies, which may be
difficult to maintain in a large application.
E.g., when a trigger is fired, a SQL statement
within its trigger action potentially can fire
other triggers. When a statement in a trigger
body causes another trigger to be fired, the
triggers are said to be cascading.
UPDATE_T1 Trigger
BEFORE UPDATE ON T1
FOR EACH ROW
BEGIN
...
INSERT INTO t2 VALUES (...);
...
END;
Fires the INSERT-T2 Trigger
INSERT_T2 Trigger
BEFORE UPDATE ON T2
FOR EACH ROW
BEGIN
...
INSERT INTO ... VALUES (...);
...
END;
Checklist for Creating Users
1. Choose a username and authentication
mechanism.
2. Identify tablespaces in which the user needs to
store objects.
3. Decide on quotas for each tablespace.
4. Assign a default tablespace and temporary
tablespace.
5. Create a user.
6. Grant privileges and roles to the user.
Creating Users
Set the initial password:
CREATE USER scott
IDENTIFIED BY tiger
DEFAULT TABLESPACE user_data
TEMPORARY TABLESPACE temp
QUOTA 15m ON user_data;
DROP USER peter CASCADE;
Monitoring Users
DBA_USERS
DBA_TS_QUOTAS
USERNAME
USERNAME
USER_ID
TABLESPACE_NAME
CREATED
BYTES
ACCOUNT_STATUS
MAX_BYTES
LOCK_DATE
BLOCKS
EXPIRY_DATE
MAX_BLOCKS
DEFAULT_TABLESPACE
TEMPORARY_TABLESPACE
System Privileges: Examples
Category
Examples
INDEX
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
TABLE
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
SELECT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
SESSION
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
TABLESPACE
CREATE TABLESPACE
ALTER TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
Displaying System Privileges
Database Level
Session Level
DBA_SYS_PRIVS
SESSION_PRIVS
• GRANTEE
• PRIVILEGE
• PRIVILEGE
• ADMIN OPTION
Object Privileges
Object priv.
Table
ALTER

DELETE

Sequence
Procedure


EXECUTE
INSERT

SELECT

UPDATE


Displaying Object Privileges
DBA_TAB_PRIVS
DBA_COL_PRIVS
GRANTEE
OWNER
TABLE_NAME
GRANTOR
PRIVILEGE
GRANTABLE
GRANTEE
OWNER
TABLE_NAME
COLUMN_NAME
GRANTOR
PRIVILEGE
GRANTABLE
GRANT create session TO scott;
REVOKE create session FROM scott;
Roles
Users
King
Roles
Roger
HR_MGR
Scott
HR_CLERK
Privileges
SELECT ON
EMP
CREATE
TABLE
INSERT ON
EMP
CREATE
SESSION
UPDATE
ON EMP
Triggers and Views
•
•
•
•
•
Triggers can be defined only on tables, not on views but triggers on the base
table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is
issued against a view.
INSTEAD OF triggers provide a transparent way of modifying views that
cannot be modified directly through SQL DML statements (INSERT, UPDATE,
and DELETE).
Oracle fires the INSTEAD OF trigger instead of executing the triggering
statement. The trigger performs update, insert, or delete operations directly on
the underlying tables.
Users write normal INSERT, DELETE, and UPDATE statements against the
view and the INSTEAD OF trigger works invisibly in the background to make
the right actions take place.
By default, INSTEAD OF triggers are activated for each row.
CREATE
SELECT
FROM
WHERE
VIEW tutor_info AS
s.name,s.studno,s.tutor,t.roomno
student s, staff t
s.tutor = t.lecturer;
Example of an INSTEAD OF Trigger
CREATE TRIGGER tutor_info_insert
INSTEAD OF INSERT ON tutor_info
REFERENCING NEW AS n -- new tutor
FOR EACH ROW
The actions shown for rows being inserted
into the TUTOR_INFO view first test to see if
appropriate rows already exist in the base
tables from which TUTOR_INFO is derived.
The actions then insert new rows or update
existing rows, as appropriate. Similar triggers
can specify appropriate actions for UPDATE
and DELETE.
BEGIN
IF NOT EXISTS SELECT * FROM student WHERE student.studno = :n.studno
THEN INSERT INTO student(studentno,name,tutor)
VALUES(:n.studno, :n.name, :n.tutor);
ELSE UPDATE student SET student.tutor = :n.tutor
WHERE student.studno = :n.studno;
END IF;
IF NOT EXISTS SELECT * FROM staff WHERE staff.lecturer = :n.tutor
THEN INSERT INTO staff VALUES(:n. staff.tutor, :n.roomno);
ELSE UPDATE staff SET staff.roomno = :n.roomno WHERE staff.lecturer =
:n.tutor;
END IF;
END;