10) ВИД, ИНДЕКС, ТРИГГЕР

.open TutorialsSampleDB.db

CREATE INDEX StudentNameIndex ON Students(StudentName);

Шаг 5) Чтобы убедиться, что индекс был создан, вы можете выполнить следующий запрос, который даст вам список индексов, созданных в таблице учеников:

PRAGMA index_list(Students);

Вы должны увидеть индекс, который мы только что создали:

Ноты:

• Индексы могут быть созданы не только на основе столбцов, но и выражений. Что-то вроде этого:

CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

«OrderTotalIndex» будет основываться на столбце OrderId, а также на умножении значения столбца «Количество» и значения столбца «Цена». Таким образом, любой запрос для «OrderId» и «Количество * Цена» будет эффективным, так как запрос будет использовать индекс.

• Если вы задали предложение WHERE в операторе CREATE INDEX, индекс будет частичным. В этом случае в индексе будут записи только для тех строк, которые соответствуют условиям в предложении WHERE. Например, в следующем индексе:

  CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
  WHERE Quantity > 10000;

  (В приведенном выше примере индекс будет частичным индексом, так как задано предложение WHERE. В этом случае индекс будет применяться только к тем заказам, количество которых превышает 10000. Обратите внимание, что этот индекс называется частичным index из-за предложения WHERE, а не из выражения, используемого в нем. Однако вы можете использовать выражения с обычными индексами.)

• Вы можете использовать инструкцию CREATE UNIQUE INDEX вместо CREATE INDEX, чтобы предотвратить повторяющиеся записи для столбцов, и, таким образом, все значения для индексированного столбца будут уникальными.
• Чтобы удалить индекс, используйте команду DROP INDEX, а затем имя индекса для удаления.

SQLite Trigger

.open TutorialsSampleDB.db

CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;	

Триггер « InsertIntoStudentTrigger » будет срабатывать каждый раз, когда вы вставляете нового ученика в таблицу учеников. Ключевое слово « new » относится к значениям, которые будут вставлены. Например, « new.StudentId » будет идентификатором студента, который будет вставлен.

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

Шаг 4) Напишите следующую команду, которая вставит нового ученика в таблицу учеников:

INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

Шаг 5) Напишите следующую команду, которая выберет все строки из таблицы « StudentsLog »:

SELECT * FROM StudentsLog;

Вы должны увидеть новую строку, возвращенную для нового студента, которого мы только что добавили:

Этот ряд был вставлен триггером перед вставкой нового студента с идентификатором 11.

В этом примере мы использовали созданный нами триггер « InsertIntoStudentTrigger », чтобы автоматически регистрировать любые транзакции вставки в таблице « StudentsLog ». Таким же образом вы можете регистрировать любое обновление или удалять операторы.

Предотвращение непреднамеренных обновлений с помощью триггеров:

Используя триггеры BEFORE UPDATE для таблицы, вы можете запретить операторы обновления для столбца на основе выражения.

пример

В следующем примере мы не допустим, чтобы какой-либо оператор обновления обновлял столбец «имя студента» в таблице «Студенты»:

Шаг 1) Перейдите в каталог « C: \ sqlite » и запустите sqlite3.exe.

Шаг 2) Откройте базу данных « TutorialsSampleDB.db », выполнив следующую команду:

.open TutorialsSampleDB.db

Шаг 3) Создайте новый триггер « warnUpdateStudentName » в таблице « Студенты », выполнив следующую команду

CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

Команда « RAISE » вызовет ошибку с сообщением об ошибке « Невозможно обновить имя студента », а затем предотвратит выполнение оператора обновления.

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

Шаг 4) Запустите следующую команду обновления, которая изменит имя студента « Джек » на « Джек1 ».

UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';	

Вы должны получить сообщение об ошибке, которое мы указали на триггере: « Вы не можете обновить имя студента » следующим образом:

Шаг 5) Запустите следующую команду, которая выберет список имен учеников из таблицы учеников.

SELECT StudentName FROM Students;

Вы должны увидеть, что имя студента «Джек» остается прежним, и оно не меняется:

Резюме:

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