W Cat - SQL за 24 часа

Триггер можно создать с помощью оператора CREATE TRIGGER. Стандарт ANSI предлагает для этого оператора следующий синтаксис.

CREATE TRIGGER TRIGGER NAME

[[BEFORE | AFTER] TRIGGER EVENT ON TABLE NAME]

[REFERENCING VALUES ALIAS LIST ]

[TRIGGERED ACTION TRIGGER EVENT ::=

INSERT UPDATE | DELETE {OF TRIGGER COLUMN LIST]

TRIGGER COLUMN LIST ::= COLUMN NAME [, COLUMN NAME]

VALUES ALIAS LIST ::=

VALUES ALIAS LIST ::=

OLD [ROW] [AS] OLD VALUES CORRELATION NAME |

NEW {ROW} {AS} NEW VALUES CORRELATION NAME |

OLD TABLE {AS} OLD VALUES TABLE ALIAS |

NEW TABLE {AS} NEW VALUES TABLE ALIAS

OLD VALUES TABLE ALIAS ::= IDENTIFIER

NEW VALUES TABLE ALIAS ::= IDENTIFIER

TRIGGERED ACTION ::=

[FOR EACH [ROW | STATEMENT] [WHEN SEARCH CONDITION]]

TRIGGERED SQL STATEMENT

TRIGGERED SQL STATEMENT ::=

SQL STATEMENT | BEGIN ATOMIC [SQL STATEMENT;]

END

В Microsoft SQL Server синтаксис оператора для создания триггера выглядит следующим образом.

CREATE TRIGGER ИМЯ_ТРИГГЕРА

ON ИМЯ_ТАБЛИЦЫ

FOR { INSERT | UPDATE | DELETE [, ..]}

AS

OnEPATOPH_SQL

[ RETURN ]

В Oracle базовый синтаксис оператора следующий.

CREATE [ OR REPLACE ] TRIGGER ИМЯ_ТРИГГЕРА

[ BEFORE | AFTER]

[ DELETE I INSERT | UPDATE]

ON [ ПОЛЬЗОВАТЕЛЬ.ИМЯ_ТАБЛИЦЫ ]

[ FOR EACH ROW ]

[ WHEN УСЛОВИЕ ]

[ БЛОК PL/SQL ]

Вот пример создания триггера.

CREATE TRIGGER EMP_PAY_TRIG

AFTER UPDATE ON EMPLOYEE_PAY_TBL

FOR EACH ROW

BEGIN

INSERT INTO EMPLOYEE_PAY_HISTORY

(EMP_ID, PREV_PAY_RATE, PAY_RATE, DATE_LAST_RAISE,

TRANSACTION_TYPE)

VALUES

(:NEW.EMP_ID, :OLD.PAY_RATE, :NEW.PAY_RATE,

:NEW.DATE_LAST_RAISE, 'PAY CHANGE');

END;

/

Триггер создан.

В этом примере создается триггер с именем EMP_PAY_TRIG. Этот триггер вставляет строку в таблицу EMPLOYEE_PAY_HISTORY, отражая изменения таблицы EMPLOYEE_PAY_TBL каждый раз, когда данные последней обновляются.

Тело триггера изменить нельзя Для этого триггер придется либо заменить другим, либо воссоздать В некоторых реализациях SQL триггер можно заменить (если триггер с данным именем в системе уже существует) с помощью того же оператора CREATE TRIGGER.

Триггер можно удалить с помощью оператора DROP TRIGGER. Синтаксис этого оператора следующий.

DROP TRIGGER ИМЯ_ТРИГТЕPA

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

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

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

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

Интерфейс уровня вызовов (call level interface) используется для внедрения программного кода SQL в главную программу, создаваемую, например, средствами ANSI С Создателям приложений понятие интерфейса уровня вызовов должно быть хорошо знакомым. Это один из методов, с помощью которого программист получает возможность внедрить SQL в программный код некоторых процедурных языков программирования. При использовании интерфейса уровня вызовов текст оператора SQL передается некоторой переменной программы с соблюдением правил соответствующего языка программирования. После этого получившая оператор SQL программа может его выполнить, обработав переменную, которой был передан текст этого оператора.

Типичной командой языка программирования, позволяющей вызвать оператор SQL из программы, является команда EXEC SQL.

Вот некоторые из языков программирования, поддерживающих интерфейс уровня вызовов.

• COBOL

• ANSI С

• Pascal

• Fortran

• Ada

По поводу использования опций интерфейса уровня вызовов обратитесь к документации по соответствующему языку программирования

Использование SQL для генерации операторов SQL в некоторых случаях значительно экономит время. Предположим, что в вашей базе данных 100 пользователей. Предположим также, что вы создаете новую роль ENABLE (пользовательский объект с соответствующими привилегиями), которую необходимо приписать всем 100 пользователям. Вместо того, чтобы вручную создавать 100 операторов GRANT, можно использовать следующий оператор SQL, который сгенерирует необходимые операторы за вас

SELECT 'GRANT ENABLE TO '|| USERNAME || ';'

FROM SYS.DBA_USERS;

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

Обратите внимание на то, что здесь фраза GRANT ENABLE TO заключена в кавычки. Использование кавычек заставляет воспринимать все заключенное между ними как буквальное значение. Буквальные значения можно выбирать из таблиц точно так же, как и столбцы. USERNAME является столбцом таблицы SYS. DBA_USERS из системного каталога. Двойная вертикальная черта ( | | ) используется для конкатенации столбцов. Использование двойной вертикальной черты с последующим '; ' добавляет к концу пользовательского имени точку с запятой, означающую завершение оператора.

В результате сгенерированные операторы SQL будут выглядеть примерно так:

GRANT ENABLE TO RRPLEW;

GRANT ENABLE TO RKSTEP;

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

В следующий раз, когда вам придется при написании операторов SQL повторять однообразные операторы SQL несколько раз подряд, остановитесь и позвольте SQL сделать эту работу за вас.

Прямое использование SQL означает выполнение операторов SQL с любого интерактивного терминала. Результаты при этом возвращаются обратно на терминал, инициировавший выполнение операторов. В данной книге до сих пор, в основном, рассматривалось прямое использование SQL. Прямое использование SQL называют также интерактивным вызовом или прямым вызовом (direct invocation)

Вюженныи SQL (embedded SQL) представляет собой программный код SQL, используемый в рамках другой программы, созданной, например, средствами Pascal, Fortran, COBOL или С Программный код SQL оказывается фактически встроенным в несущий его язык программирования посредством интерфейса уровня вызовов, как было указано выше. Встроенные в несущий язык программирования операторы SQL обычно предваряются командой ЕХЕС SQL и, как правило, завершаются точкой с запятой. В других же случаях такими завершающими командами могут быть END-EXEC или закрывающая скобка.

Вот пример вложения оператора SQL в вызывающую программу, созданную средствами ANSI С

{операторы вызывающей программы}

ЕХЕС SQL {оператор SQL};

{другие операторы вызывающей программы}

В ходе этого урока обсуждались некоторые средства SQL, выходящие за рамки элементарных. Хотя мы и не вдавались в детали, вы должны были получить общее представление о том, как используются обсуждавшиеся здесь возможности. Сначала были рассмотрены курсоры, с помощью которых можно сохранять результаты запросов в памяти. Чтобы объявившая курсор программа могла его использовать, эта программа должна открыть его. После этого содержимое курсора можно передать соответствующей переменной, чтобы программа смогла в нужный момент эти данные использовать. Данные курсора сохраняются в памяти до тех пор, пока курсор не будет закрыт, а занятая им память освобождена.

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