PDF de programación - Bases de Datos Activas

Bases de Datos Activas

Miguel Rodríguez Penabad

Laboratorio de Bases de Datos

Universidade da Coruña

Introducción
BD Pasivas vs. Activas

Bases de datos pasivas

I Son meros almacéns de datos
I Toda a xestión é explícita
I Hai controles mínimos (clave primaria, foránea)

Bases de datos activas

I Reaccionan ante eventos (normalmente cambios nos datos)
I Utilidade:

I Control de restriccións

Reglas de negocio: Un empleado non pode ganar máis que o
seu xefe
Validacións: NIF correcto, sexo é H ou M, ...

I Actualización de outros datos

Tabla facturas con campo total autoactualizado

I Accións externas á base de datos

I A actividade impleméntase normalmente con triggers

(disparadores)

Bases de Datos Activas

2 / 15

Introducción
Actividade sen triggers

Restriccións nivel de táboa

I Integridade de
entidade/clave

I Integridade referencial
I Restricción unique
I Restricción check

CREATE TABLE EMP(

Limitacións

I A acción sempre é abortar
I CHECK sen subconsultas en
SXBD comerciais (SQL:2003
permíteas)

I Non reacciona ante borrado

de las (excepto FK)

EMPNO NUMERIC(3) NOT NULL,
ENAME CHAR(20),
EMAILADDRESS CHAR(25) UNIQUE,
MGR NUMERIC(3), DEPTNO NUMERIC(3),
SAL NUMERIC(7,2),
CONSTRAINT PK_EMP PRIMARY KEY(EMPNO),
CONSTRAINT FK_EMP FOREIGN KEY(MGR)

REFERENCES EMP(EMPNO) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT C_SAL_POS CHECK(SAL > 0) );

Bases de Datos Activas

3 / 15

Introducción
Actividade sen triggers

Restriccións a nivel de BD: Assertions

I CREATE ASSERTION <nome> CHECK <condicion>

CREATE ASSERTION as_salarios_similares
CHECK( ( (SELECT MAX(SAL) FROM EMP)-

(SELECT MIN(SAL) FROM EMP) ) <100);

CREATE ASSERTION deptos_pequenos

CHECK (NOT EXISTS

(SELECT DEPTNO FROM EMP

GROUP BY DEPTNO
HAVING COUNT(*)>20));

Limitacións

I Poucos (ou ningún) SXBD as implementan
I A reacción sempre é abortar

Bases de Datos Activas

4 / 15

Triggers
As reglas ECA

Trigger = Disparador = Regla ECA

Evento: Cando se produce, o trigger dispárase.

I (DML) Modicación de datos: INSERT, DELETE, UPDATE
I Outros eventos: conexión á BD, etc.

Condición: (Opcional) Debe vericarse para que se execute a
acción.
Acción: Que se executa como resposta ó evento cando se da a
condición

Exemplos

I Control de Nif (?)

E: Inserción ou Actualización
C: NIF incorrecto
A: Non permitir a inserción/actualización

I Actualizar total en táboa factura (?)

E: Inserción, Borrado, Modicación (táboa Liña)
C:
A: Actualizar total (táboa Factura)
evento ⇒ 2 triggers (NIF) e 3 triggers(Facturas)

I Segundo SQL:1999 e SQL:2003, un trigger controla un único

Bases de Datos Activas

5 / 15

Triggers

Ventaxas dos triggers

I Simplica a codicación de aplicacións de acceso a BDs
I Maior consistencia (control centralizado)

Triggers no estándar SQL

I Dentro do estándar ISO/IEC 9075 Part 2: (SQL/Foundation)
I Especicados no SQL:1999 (antes SQL-3)
I Manténense no SQL:2003
I Un trigger [DML] controla unha única táboa
I Un trigger responde a un único evento

Bases de Datos Activas

6 / 15

Triggers en SQL:2003
Elementos dun trigger

I O nome do trigger
I O nome da táboa
I O evento

I Inserción (INSERT)
I Borrado (DELETE)
I Modicación(UPDATE [OF _lista_columnas_])

I O tempo de activación

I Antes (BEFORE)
I Despois (AFTER)

I Granularidade

I A nivel sentencia (STATEMENT)
I A nivel la (ROW)

Bases de Datos Activas

7 / 15

Triggers en SQL:2003
Elementos dun trigger (ii)

I Táboas e variables de transición

I Valores antigos (OLD) e novos (NEW)
I Táboa de transición: conxunto de las afectadas

OLD TABLE, NEW TABLE

EMP antes
EMPNO MGR
1000
1000
1300
1100

0001
0002
0010
1000

SAL
1500
1600
1550
3000

=⇒

UPDATE EMP

SET SAL=3100

WHERE SAL>1540

EMP despois

EMPNO MGR
1000
1000
1300
1100

0001
0002
0010
1000

SAL
1500
3100
3100
3100

I Variable de transición: recorre as las da táboa de transición

OLD ROW, NEW ROW

I A acción

I Inclúe a condición (pode omitirse)
I Debe ser atómica
I O seguinte non se recomenda (aberto a implementación):
I Sentencias de modicación de datos en triggers BEFORE

(Podemos usar SET <nome new row> = <valor>)

I Sentencias de control de transaccións, conexión a BD, ou DDL

Bases de Datos Activas

8 / 15

Triggers en SQL:2003
Exemplos

Exemplo 1: DNI correcto

I O nome do trigger: t_dni_correcto_ins
I O nome da táboa: persoa
I Evento: inserción (outro trigger para actualización)
I O tempo de activación: AFTER
I Granularidade: FILA
I Táboas e variables de transición

OLD/NEW TABLE: Non as necesitamos
OLD ROW: Non (non existe)
NEW ROW: Si

I Acción

I Condición: NIF incorrecto
I Acción: Borrar a persoa insertada

Bases de Datos Activas

9 / 15

Triggers en SQL:2003
Exemplos

Exemplo 2: Un empregado non debe cobrar máis que o seu
xefe (actualización)

I O nome do trigger: t_mal_salario_upd
I O nome da táboa: empregado
I Evento: actualización (outro trigger para inserción)
I O tempo de activación: BEFORE
I Granularidade: FILA
I Táboas e variables de transición

OLD/NEW TABLE: Non as necesitamos
OLD ROW: Si
NEW ROW: Si

I Acción

I Condición: O empregado actualizado ten xefe, e este cobra

menos, ou o empregado actualizado é un xefe, e ten
subordinados que cobran máis

I Acción: Deixar o salario que tiña o empregado

I Pregunta: Implicacións de facer este trigger de tipo after

Bases de Datos Activas

10 / 15

Triggers en SQL:2003

Para borrar un trigger:

DROP TRIGGER <nombre_trigger>;

Bases de Datos Activas

11 / 15

Triggers en SQL:2003
Exemplos

Exemplo 1: O salario dos empregados debe ser positivo
Táboa:
EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO))

I Usando restricción

ALTER TABLE Emp

ADD CONSTRAINT sal_pos
CHECK (sal>0)

I Usando assertions

CREATE ASSERTION emp_sal_pos

CHECK (NOT EXISTS (SELECT *

FROM Emp
WHERE sal<=0))

Comentarios

I Funcionan tanto para INSERT como para UPDATE
I Comproban os datos existentes na BD
I Poden ser diferidas ata o n da transacción
I Provocan erro + rollback

Bases de Datos Activas

12 / 15

CREATE TRIGGER<nome_trigger>{BEFORE| AFTER }{INSERT | DELETE | UPDATE [OF <columnas>] } ON <táboa>[REFERENCING OLD [ROW] [AS] <fila_vella> NEW [ROW] [AS] <fila_nova> OLD TABLE [AS] <táboa_vella> NEW TABLE [AS] <táboa_nova>[{ FOR EACH ROW | FOR EACH STATEMENT }][ WHEN ( <condición> ) ]{< Sentencia SQL> | BEGIN ATOMIC <Sentencia SQL 1>; ... <Sentencia SQL N> END }EventoCondiciónAcciónNomes de CorrelaciónGranularidade Triggers en SQL:2003
Exemplos

Exemplo 1 (cont.): O salario dos empregados debe ser positivo

I Usando triggers

I Para a inserción, evitando a inserción

CREATE TRIGGER sal_pos_ins

AFTER INSERT ON Emp
REFERENCING NEW ROW AS nova
FOR EACH ROW
WHEN (nova.sal <=0)
DELETE FROM Emp

WHERE empno = nova.empno;

I Para a actualización, deixando o salario anterior

¾Facer un novo update?

CREATE TRIGGER sal_pos_upd1

AFTER UPDATE OF sal ON Emp
REFERENCING NEW ROW AS nova

OLD ROW AS vella

FOR EACH ROW
WHEN (nova.sal <=0)
UPDATE Emp SET sal = vella.sal

WHERE empno = nova.empno;

Mellor cambiar o valor na la actual
CREATE TRIGGER sal_pos_upd2

BEFORE UPDATE OF sal ON Emp
REFERENCING NEW ROW AS nova

OLD ROW AS vella

FOR EACH ROW
WHEN (nova.sal <=0)
SET nova.sal = vella.sal;

Bases de Datos Activas

13 / 15

Triggers en SQL:2003
Consideracións sobre os triggers

I A creación do trigger non executa o trigger

I O trigger só se executa cando ocurre o evento e se cumpre a

condición

I Non é posible executar directamente un trigger

I O trigger non valida datos existentes na BD (as restriccións si)
I Os triggers do exemplo anterior permiten salarios negativos se

xa estaban na táboa

I A execución do trigger non evita que se execute o evento

disparador

I Triger before <evento>: execútase o trigger e logo o evento
I Triger after <evento>: execútase o evento e logo o trigger

Bases de Datos Activas

14 / 15

Exercicios

1. Un empregado non pode ganar máis ca o seu xefe
2. Un departamento non pode ter máis de 10 empregados

3. Manda un correo ó suministrador cando o stock dun artigo

baixa do límite (ARTIGO(CODART,PREZO,STOCK, MIN,
SUMINISTRADOR), procedemento
PideArtigo(suministrador, codigo))

4. Actualiza FACTURA(NUMERO, TOTAL) cando se engaden,

borran ou modican liñas (LIÑA(NUMFAC, NUMLI,IMPORTE))

Bases de Datos Activas

15 / 15