НОУ ИНТУИТ | Распределенные базы и хранилища данных. Лекция 11: Триггеры. Основные понятия. Типы триггеров. Общая схема активизации триггеров

Опубликован: 11.03.2009 | Доступ: свободный | Студентов: 2843 / 723 | Оценка: 4.22 / 4.22 | Длительность: 11:41:00

Тема: Базы данных

Использование предикатов inserting, ... и new, old

drop table test_log;
create table test_log(op_user varchar2(100), 
  op_date date, i integer, s varchar2(100));

create or replace trigger t_ud
before update of i, s or delete
on test
for each row
begin
    if updating('s') then
        if :new.s > :old.s then
            raise_application_error(-20000, 'update error');
        end if;
    elsif deleting then
        insert into test_log values(user, sysdate, :old.i, :old.s);
    end if;
end;

Выполнение триггреров в той же транзакции

delete from test;
select * from test_log;
rollback;
select * from test_log;

Активизация и деактивизация триггеров

alter trigger t_ud disable;
alter table test enable all triggers;

Получение информации о триггерах

select * from user_triggers;
select * from user_objects;

Запрет изменения new, old в after ... и table level триггерах

create or replace trigger ta_ud
after update
on test
for each row
begin
    :new.s := 'aaaaaaa';
end;

create or replace trigger ta_ud
before update
on test
for each row
begin
    :old.s := 'aaaaaaa';
end;

create or replace trigger tb_ud
before update
on test
begin
    :new.s := 'aaaaaaa';
end;

Несколько триггеров одного типа

create or replace trigger ta_ud1
after update
on test
for each row
begin
    dbms_output.put_line('trigger update 1');
end;

create or replace trigger ta_ud2
after update
on test
for each row
begin
    dbms_output.put_line('trigger update 2');
end;

Использование исключений

create or replace package p_exc
as
    invalid_str exception;
end;

create or replace trigger t_ui
before insert or update
on test
for each row
begin
    if :new.s < 'c' then
        raise p_exc.invalid_str;
    end if;
end;

begin
    delete from test;
    commit;
    insert into test values(1, 'aaa');
exception
    when p_exc.invalid_str then
        dbms_output.put_line
          ('invalid_str: ' || sqlcode || ' ' || sqlerrm);
end;

Триггеры instead of

create table tp(i integer primary key);
create table tc(k integer primary key, i integer references tp(i));

begin
    commit;
    delete from tc;
    delete from tp;
    commit;
    insert into tp values(1);
    insert into tp values(2);
    insert into tc values(5, 1);
    insert into tc values(6, 1);
    insert into tc values(7, 2);
    commit;
end;

create or replace view v as select tp.i ii, tc.k kk from 
  tp inner join tc on tp.i = tc.i;

select * from user_updatable_columns where table_name = 'V';

create or replace trigger vt
instead of insert
on v
for each row
declare
    ii integer;
begin
    begin
        select i into ii from tp where i = :new.ii;
    exception
        when no_data_found then
        insert into tp(i) values(:new.ii);
    end;

    update tc set i = :new.ii where k = :new.kk;

    if sql%rowcount = 0 then
        insert into tc(k, i) values(:new.kk, :new.ii);
    end if;
end;

Изменяющиеся таблицы

create table grp(gn number primary key, gname varchar2(10));
create table std(sn number primary key, 
  gn number references grp, sname varchar2(100));

При добавлении группы студента в группу с максимальным кол-вом студентов, перенаправляем его в группу с минимальным кол-вом студентов

create or replace trigger std_i_a
after insert
on std
for each row
declare
    n number;
begin
    dbms_output.put_line('OK insert after');
    select count(*) into n from std where gn = :new.gn;
    dbms_output.put_line('n = ' || n);
end;

create or replace trigger std_i_b
before insert
on std
for each row
declare
    n number;
begin
    dbms_output.put_line('OK insert before');
    select count(*) into n from std where gn = :new.gn;
    dbms_output.put_line('n = ' || n);
end;

create or replace trigger std_i_a_all
after insert
on std
declare
    n number;
begin
    dbms_output.put_line('OK insert after all');
    select count(*) into n from std;
    dbms_output.put_line('n = ' || n);
end;

create or replace trigger std_i_b_all
before insert
on std
declare
    n number;
begin
    dbms_output.put_line('OK insert before all');
    select count(*) into n from std;
    dbms_output.put_line('n = ' || n);
end;

Практические занятия

Постановка задачи. Проектирование данных на концептуальном и логическом уровнях. Нормализация отношений.

Презентация по ER-моделированию

ER нотации

Пример модели в ERwin

Подготовка SQL скриптов генерации схемы отношений БД в ERwin. Разработка скрипта для ввода тестовой информации.

Видео-презентация (Для проигрывания требуется Windows Media Player)

Архитектура MS SQL Server 2005. Настройка и использование основных компонент среды. Создание учебной базы данных.

Видео-презентация (Для проигрывания требуется Windows Media Player)

Работа с СУБД MS SQL Server 2005, ORACLE 10g. Примеры соединений с БД, технологии разработки клиенского приложения

Использование технологии Java Database Connectivity (JDBC) для работы с базами данных

Презентация

Примеры к презентации

SQL-скрипты, проект и исходные коды

package org.mai806.jdbcsample;

import java.sql.*;

public class QuerySample {
    
    public static void main(String[] args) throws Exception {
        
        /* ======== Подключение к MS SQL Server ===== */
        // Загрузка драйвера
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        // Соединение с базой данных
        Connection connection = DriverManager.getConnection(
                "jdbc:sqlserver://localhost:1433;databaseName=o01;", 
                // localhost - сервер СУБД, o01 - имя базы данных
                "sa", "123"); // пользователь, пароль
        
        /* ======== Подключение к Oracle ============
        // Загрузка драйвера
        Class.forName("oracle.jdbc.OracleDriver");
        // Соединение с базой данных
        Connection connection = DriverManager.getConnection(
                        "jdbc:oracle:thin:@localhost:1521:orcl", 
                        // localhost - сервер СУБД, orcl - SID базы оракла
                        "o01", "o01"); // пользователь, пароль         
        
        // Создание Statement
        PreparedStatement stmt = connection.prepareStatement
        ("select ID, NAME from PERSON where NAME like ?");
        stmt.setString(1, "%S%");
        // Выполнение запроса
        ResultSet rs = stmt.executeQuery();
        // Перебор результата выполнения запроса
        while(rs.next()) {
            // Пример выбора параметра по номеру или по имени
            System.out.println("ID: " +
                    rs.getLong(1) + "; NAME="+
                    rs.getString("NAME"));
        }
        
        // закрытие использованных ресурсов БД
        rs.close();
        stmt.close();
        connection.close();
    }
}

Листинг P.1. Выполнение запроса: QuerySample.java

package org.mai806.jdbcsample;

import java.sql.*;
import java.util.ResourceBundle;

public class StoredProcedureSample {
    
    private static Connection connection = null;
    public static void main(String[] args) throws Exception {
        // Получение соединения из значений параметров в файле properties
        ResourceBundle properties = ResourceBundle.getBundle("database");
        Class.forName(properties.getString("driver"));
        connection = DriverManager.getConnection(
                properties.getString("url"),
                properties.getString("user"),
                properties.getString("password"));
        
        transferAmount(1,2,100.0);
        connection.close();
    }
    
    /**
     * Переводит указанную сумму с одного счета на другой
     * @param from счет плательщика
     * @param to счет получателя
     * @param amount сумма
     */
    public static void transferAmount(long from, long to, double amount)
    throws Exception {
        // Создание Statement
        CallableStatement stmt
                = connection.prepareCall("{call TransferAmount(?,?,?)}");
        // Установка параметров
        stmt.setLong(1, from);
        stmt.setLong(2, to);
        stmt.setDouble(3, amount);
        // Выполнение процедуры
        stmt.execute(); 
    }
}

Листинг P.2. Выполнение хранимой процедуры: StoredProcedureSample.java

package org.mai806.jdbcsample;

import java.sql.*;
import java.util.ResourceBundle;

public class TransactionalSample {
    
    private static Connection connection = null;
    public static void main(String[] args) throws Exception {
        
        // Получение соединения из значений параметров в файле properties
        ResourceBundle properties = ResourceBundle.getBundle("database");
        Class.forName(properties.getString("driver"));
        connection = DriverManager.getConnection(
                properties.getString("url"),
                properties.getString("user"),
                properties.getString("password"));
        
        // Ручное управление транзакциями
        connection.setAutoCommit(false);
        
        try {
            transferAmount(2, 1, 10.0);
        } finally {
            connection.close();
        }
    }
    
    /**
     * Переводит указанную сумму с одного счета на другой
     * @param from счет плательщика
     * @param to счет получателя
     * @param amount сумма
     */
    public static void transferAmount(long from, long to,
            double amount) throws Exception {
        PreparedStatement stmt = null;
        Statement query = null;
        try {
            stmt = connection.prepareStatement
            ("update ACCOUNT set AMOUNT=AMOUNT+? where ID=?");
            // Забираем сумму со счета плательщика
            stmt.setDouble(1, -amount);
            stmt.setLong(2, from);
            stmt.execute();
            
            // Добавляем сумму на счет получателя
            stmt.setDouble(1, amount);
            stmt.setLong(2, to);
            stmt.execute();
            
            // Пост-проверка: отрицательность счета плательщика
            query = connection.createStatement();
            ResultSet rs = query.executeQuery(
                    "select AMOUNT from ACCOUNT where ID="+from+" and AMOUNT<0");
            if (rs.next()) {
                throw new Exception("На счете №"+from+" 
                недосточно средств ["+(amount+rs.getDouble(1))+"] 
                для снятия суммы ["+amount+"]");
            }
            connection.commit();
            System.out.println("Перечисление средств успешно выполнено");
        } catch(Exception e) {
            e.printStackTrace();
            connection.rollback();
        } finally {
            if (stmt!=null)
                stmt.close();
            if (query!=null)
                query.close();
        }
    }
    
}

Листинг P.3. Работа с транзакциями: TransactionalSample.java

Авторизоваться

Распределенные базы и хранилища данных

Триггеры. Основные понятия. Типы триггеров. Общая схема активизации триггеров