Главная страница
Навигация по странице:

  • Вызов функции: select GetMark1(68); Пример 2.

  • Упражнение 1. Создание триггера

  • Прежде чем удалять данные из таблицы Договоры, запомните данные, которые там находятся

  • Практическое занятие 13. New переменная типа record, в которой содержится новая строка для insertupdateтриггеров типа for each row или значение null для триггеров типа for each statement и для insertupdate триггеров типа for each row old


    Скачать 32.02 Kb.
    НазваниеNew переменная типа record, в которой содержится новая строка для insertupdateтриггеров типа for each row или значение null для триггеров типа for each statement и для insertupdate триггеров типа for each row old
    Дата13.04.2019
    Размер32.02 Kb.
    Формат файлаdocx
    Имя файлаПрактическое занятие 13.docx
    ТипДокументы
    #73648

    Подборка по базе: выбор типа гл суд передачи.pdf, Методы регулирования нагнетателей объемного типа.docx, СОП «Алгоритм применения ультрафиолетовых бактерицидных облучате, Вопросы для самостоятельной подготовки к занятиям лекционного т, Устройство бочки стальной типа БС.docx, Защита чести и достоинства от публикаций типа Панамского скандал, Гендерные особенности отношения к соматическому заболеванию на п, Гендерные особенности отношения к соматическому заболеванию на п, Гендерные особенности отношения к соматическому заболеванию на п, статья о типах пищевого поведения.docx.

    Триггеры

    Триггеры – это специальный тип функций, которые выполняются автоматически при выполнении инструкций языка манипулирования данными (INSERT, UPDATE, DELETE). Триггеры обычно используются для внесения каскадных изменений в связанные таблицы или для выполнения сложных ограничений, которые нельзя реализовать стандартными средствами SQL.

    Триггеры делятся на два вида: триггеры (FOR EACH ROW), вызываемые для каждой из строк, участвующих в операции, и триггеры (FOR EACH STATEMENT), которые вызываются один раз для каждой операции.

    Триггеры также могут вызываться до (before-триггеры) начала выполнения операции и после (after-триггеры).

    Триггеры создаются с помощью команды CREATE TRIGGER, в которой указывается, какая функция будет выполняться при вызове триггера. Эти функции могут создаваться на любом из поддерживаемых процедурных языков, например, на PL/pgSQL. Они должны быть определены без

    параметров и с возвращаемым значением типа trigger. Когда такая функция запускается на исполнение, в ее основном блоке (блок самого верхнего уровня) создаются несколько переменных, в которых фиксируются параметры соответствующего триггера, и две буферные переменные:

    NEW - переменная типа RECORD, в которой содержится новая строка для INSERT/UPDATE-триггеров типа FOR EACH ROW или значение NULL для триггеров типа FOR EACH STATEMENT и для INSERT/UPDATE- триггеров типа FOR EACH ROW;

    OLD - переменная типа RECORD, в которой содержится старая строка для UPDATE/DELETE-триггеров типа FOR EACH ROW или значение NULL для триггеров типа FOR EACH STATEMENT и для INSERT-триггеров типа FOR EACH ROW.
    Примеры:

    /* Создаем таблицу */

    CREATE TABLE emp ( a text NOT NULL,

    b integer NOT NULL CHECK (b>0),

    last_date timestamp,

    last_user text );

    /* Создаемтриггернуюфункцию*/

    CREATE FUNCTION stamp() RETURNS trigger AS $stamp$

    BEGIN

    -- Проверка a= null и b= null и b>0

    IF NEW.a IS NULL THEN

    RAISE EXCEPTION 'a не может бать null !';

    END IF;

    IF NEW.b IS NULL THEN

    RAISE EXCEPTION ' b не может бать null ! ', NEW.a;

    END IF;

    IF NEW.b < 0 THEN

    RAISE EXCEPTION ‘ b = % должно быть положительным !', NEW.b;

    END IF;

    -- Заносим в таблицу текущее время и текущего пользователя

    NEW.last_date := current_timestamp;

    NEW.last_user := current_user;

    RETURN NEW;

    END;

    $stamp$

    LANGUAGE plpgsql;
    / * Создаемтригер */

    CREATE TRIGGER stamp BEFORE INSERT OR UPDATE ON emp

    FOR EACH ROW EXECUTE PROCEDURE stamp();
    Для создания процедуры, функции или триггера требуется воспользоваться контекстным меню соответствующего элемента дерева. Для создания и редактирования существует специальное диалоговое окно, в котором, в частности, можно задать программный код процедуры, функции или триггера. Программный код формируется посредством перемешивания команд управления и SQL-команд.

    Теперь приведем несколько примеров создания хранимых процедур и функций. Здесь мы уже заметим существенные отличия в синтаксисе используемых команд для различных СУБД. Поэтому для каждого из СУБД текст процедур, функций, триггеров и способы вызова укажем отдельно.
    Пример 1. Напишем хранимую процедуру, которая получает в качестве входного параметра количество баллов и на основании шкалы оценок вычисляет полученную оценку. Результат возвращается через выходной параметр.

    PostgreSQL не позволяет создавать процедуры. Здесь используются только функции. Еще одна особенность состоит в том, что функцию можно написать на разных языках. Наиболее распространены sql и plpgsql. Основное отличие языков состоит в том, что в sql доступны только операторы sql, а plpgsql имеет также операторы управления. Интересно, что именовать параметры вовсе не обязательно. К параметрам можно обращаться по номерам, предваренным символом “$”. Итак, создадим скрипт, в котором запишем следующую функцию:

    CREATE FUNCTION GetMark1 (integer) RETURNS integer AS $$

    DECLARE res INTEGER;

    BEGIN

    IF $1 BETWEEN 55 AND 70 THEN

    SELECT 3 INTO res;

    ELSE IF $1 BETWEEN 71 AND 85 THEN

    SELECT 4 INTO res;

    ELSE IF $1 BETWEEN 86 AND 100 THEN

    SELECT 5 INTO res;

    ELSE SELECT 2 INTO res;

    END IF; END IF; END IF;

    RETURN res;

    END;

    $$ LANGUAGE plpgsql;

    Отметим применение символов “$$” в начале и конце функции. Они позволяют игнорировать символы-разделители внутри этих своеобразных скобок. Функция декларирует тип возвращаемого значения с помощью ключевого слова RETURNS. В теле функции создается переменная для хранения результата, которой присваивается значение в зависимости от ветки условных операторов, по которой пойдет управление. К параметру производится обращение посредством “$1”. Отметим еще, что в конце следует указать используемый язык написании функции.

    Вызов функции:

    select GetMark1(68);
    Пример 2. Чтобы при смене правил вычисления оценок не нужно было бы менять процедуру, мы создали справочную таблицу для хранения всех оце-ънок и их диапазонов Marks. Пришло время ею воспользоваться. Второй вариант функции получения оценки по набранным баллам будет обращаться к этой таблице за информацией.

    Оформим этот вариант в виде функции с одним параметром, хранящим набранные баллы, и возвращающую найденную оценку или 2 в случае, когда набранным баллам ничего в таблице не соответствует. В данной функции демонстрируется использование переменных, запросов и условного оператора. Приведем два варианта функции. С помощью запроса на количество таких записей алгоритм первого варианта предусматривает определение, есть ли в таблице соответствующая баллам оценка Второй вариант пользуется специальной функцией EXISTS, которая, принимая в качестве аргумента запрос, возвращает логическое значение, определяющее, есть ли в результате запроса записи.

    Как уже было сказано, в PostgreSQL хранимых процедур нет, в этом СУБД используются только функции.

    CREATE FUNCTION GetMark2 (integer) RETURNS integer AS $$

    DECLARE kolvo INTEGER;

    DECLARE mark INTEGER;

    BEGIN

    SELECT 2 INTO mark;

    SELECT COUNT(*) INTO kolvo FROM "Marks"

    WHERE $1 between "LowBalls" and "HighBalls";

    IF kolvo>0 THEN

    SELECT "idMark" INTO mark FROM "Marks"

    WHERE $1 between "LowBalls" and "HighBalls";

    END IF;

    RETURN mark;

    END;

    $$ LANGUAGE plpgsql;
    CREATE FUNCTION GetMark3 (integer) RETURNS integer AS $$

    DECLARE mark INTEGER;

    BEGIN

    SELECT 2 INTO mark;

    IF EXISTS (SELECT * FROM "Marks"

    WHERE $1 between "LowBalls" and "HighBalls") THEN

    SELECT "idMark" INTO mark FROM "Marks"

    WHERE $1 between "LowBalls" and "High-Balls";

    END IF;

    RETURN mark;

    END;

    $$ LANGUAGE plpgsql;
    Теперь приведем примеры создания триггеров.

    Пример 1. Создадим триггер для вставки в таблицу результатов сессии, в котором проверяются ограничения целостности (студент с заданном кодом существует, предмет с заданным кодом существует, дисциплину нужно сдавать именно в этом семестре). Если произойдет нарушение этих ограничений, то требуется откатить транзакцию, т.е. не выполнять вставку записи. Если же все данные будут корректными, проведем заполнение значений полей даты сдачи зачета/экзамена как текущей и вычислим оценку по указанным баллам.

    Для проверки корректности данных для вставки создадим вспомогательную хранимую функцию, чтобы код триггера был не очень сложным. (Для некоторых версий СУБД требуется, чтобы в триггере было упоминание только текущей записи, обращение к другим таблицам и записям недоступно).

    CREATE FUNCTION IsCorrect(integer, integer, integer, integer)

    RETURNS BOOLEAN AS $$

    BEGIN

    RETURN EXISTS (SELECT * from "Students" INNER JOIN "Sessions"

    ON "Students"."NumGroup"="Sessions"."NumGroup"

    INNER JOIN "Subjects" ON

    "Sessions"."idSubject"="Subjects"."idSubject"INNER JOIN "Teachers" ON

    "Sessions"."idTeacher"="Teachers"."idTeacher"

    WHERE "Students"."idStudent"=$1 AND

    "Subjects"."idSubject"=$2

    AND "Teachers"."idTeacher"=$3 AND "NumSemestr"=$4);

    END;

    $$ LANGUAGE plpgsql;
    Триггер на вставку записи в таблицу Results будет вызывать функцию проверки корректности, передавая в функцию поля из новой записи. Если запись будет корректной, будут скорректированы поля оценки и даты сдачи зачета/экзамена. В противном случае должен быть произведен откат транзакции.

    В PostgreSQL триггер как таковой связан со специальной триггерной функцией, в которой и осуществляется вся обработка данных. Триггерная функция возвращает объект-запись (NEW или OLD), с которой производится работа. При написании триггера мы указываем только для какой операции, для какой таблицы и каков тип триггера, после чего вызываем триггерную функцию. Откат производится генерацией исключительной ситуации с указанием сообщения об ошибке

    :

    -- Создание триггерной функции на вставку результата сдачи экзамена

    CREATE FUNCTION trigger_results_insert() RETURNS trigger AS $$

    BEGIN

    IF IsCorrect(NEW."idStudent", NEW."idSubject",

    NEW."idTeacher", NEW."NumSemestr")

    THEN

    SELECT GetMark3(NEW."Balls") INTO NEW."Mark";

    SELECT Now() INTO New."DateExam";

    ELSE

    -- генерация исключительной ситуации

    RAISE EXCEPTION 'Ошибка корректности данных';

    END IF;

    RETURN NEW;

    END;

    $$ LANGUAGE plpgsql;

    -- Создание триггера на вставку нового результата экзамена

    CREATE TRIGGER tr_results_insert

    BEFORE INSERT ON "Results" FOR EACH ROW

    EXECUTE PROCEDURE trigger_results_insert();
    Для проверки работы триггера проведем следующие операции вставки:

    INSERT INTO Results (idStudent, idSubject,idTeacher, NumSemestr, Balls) VALUES (1,1,1,1,78);

    INSERT INTO Results (idStudent, idSubject,idTeacher, NumSemestr, Balls) VALUES (2,1,1,1,98);

    INSERT INTO Results (idStudent, idSubject,idTeacher, NumSemestr, Balls) VALUES (6,1,1,1,68);

    Согласно данным, которые мы вносили в таблицу, последняя запись не должна быть добавлена.
    Пример 2. Приведем еще один пример триггера на вставку новой записи в таблицу результатов. Этот триггер должен срабатывать после вставки и быть связан с подсчетом рейтинга студентов. Триггеры «после» часто используются для проведения специальной обработки данных на основании выполненной операции и могут быть связаны с другими таблицами.

    Для этого введем в базу данных новую таблицу, например, с помощью следующей SQL-команды:

    CREATE TABLE Reyting

    ( idStudent INT PRIMARY KEY,

    summ_balls INT,

    CONSTRAINT fk_reyting

    FOREIGN KEY (idStudent) REFERENCES Students (idStudent)

    )

    При вставке нового результата рейтинг студента должен меняться. Таким образом, нужно проанализировать, есть ли запись о студенте – в случае положительного ответа произвести суммирование баллов, иначе добавить новую запись в таблицу рейтинга.
    CREATE FUNCTION trigger_results_insert_after() RETURNS trigger AS $$

    BEGIN

    IF EXISTS(SELECT * FROM "Reyting" WHERE "idStudent"=NEW."idStudent") THEN

    UPDATE "Reyting" SET "summ_balls"="summ_balls"+NEW."Balls"

    WHERE "idStudent"=NEW."idStudent";

    ELSE

    INSERT INTO "Reyting" ("idStudent", "summ_balls")

    VALUES (NEW."idStudent", NEW."Balls");

    END IF;

    RETURN NEW;

    END;

    $$ LANGUAGE plpgsql;

    CREATE TRIGGER tr_results_insert_after

    AFTER INSERT ON "Results" FOR EACH ROW

    EXECUTE PROCEDURE trigger_results_insert_after();
    . Упражнение 1. Создание триггера

    В этом упражнении вы создадите триггер для сохранения и контрольного отслеживания изменений. Для работы используйте БД со своей фамилией.

    1. Создайте триггер, который после внесения новых данных в таблицу Сотрудники, выводит на экран сообщение о том, что «В таблицу были внесены изменения», а также выводит новые данные на экран




    1. Создайте триггер, который при попытке удаления строки из таблицы Договоры, выводит сообщение, что удаление невозможно.


    Прежде чем удалять данные из таблицы Договоры, запомните данные, которые там находятся. Проверить работу триггера можно путем удаления некоторых строк из таблицы.

    1. Создайте триггер, который контролирует изменение стоимости услуг. Если происходит изменение стоимости на меньшую, то происходит откат операций, выводится сообщение о том, что стоимость меньше прежней.



    В противном случае, выводит новую цену и сообщение, что изменилась стоимость услуги или товара.


    написать администратору сайта