SQL Command and Function Reference - DBMaker Online / Manuals

CREATE TRIGGER

Creates a new trigger on a table.

SYNTAX

trigger_name	Name of the table you want to create.
column_name	Name of the column you want to create the trigger on.
table_name	Name of the table you want to create the trigger on..
sql_statement	Statement to execute when the trigger fires.

DESCRIPTION

The CREATE TRIGGER command creates a new trigger on an existing table. You can use triggers to customize your database in ways that would not be possible with standard SQL commands. To execute the CREATE TRIGGER command on a table you must be the table owner, or have DBA or SYSADM security privileges, and have all security and object privileges necessary to execute the SQL statement that defines the trigger action.

A trigger is a database server mechanism that automatically executes predefined commands in response to specific events. This allows a database to perform complex or unconventional operations that might not be possible using standard SQL commands. Since triggers are under the control of the database server, they can ensure data is handled consistently regardless of the source. Once you create a trigger on a table, its operation is transparent to users of the database; DBMaker will fire the trigger every time a user or application program generates a trigger event.

When you create a trigger you must specify the trigger name, the trigger action time (when a trigger should fire relative to the trigger event), the trigger event (the event that causes the trigger to operate, or fire), the trigger table (the table you are creating the trigger on), the trigger type (the type of trigger you want to fire), and the trigger action (the action the database should take whenever the trigger fires). Any triggers you create on a table are dropped automatically if you drop the table.

Unlike most database objects, DBMaker does not identify triggers using fully qualified names (a combination of owner and object names), but associates them with tables instead. For this reason all trigger names on the same table must be unique. The trigger action operates with the same security and object privileges as the owner of the trigger table, and not with the privileges of the user executing the trigger event.

The BEFORE/AFTER keywords specify when the database server should perform the trigger action relative to the trigger event. (This is known as the trigger action time.) The BEFORE keyword specifies that the database server should perform the trigger action before the trigger event, and the AFTER keyword specifies that the database server should perform the trigger action after the trigger event.

The INSERT/DELETE/UPDATE keywords specify the event that fires a trigger. (This is known as the trigger event.) There are some differences in the use of the INSERT and DELETE keywords, and the UPDATE keyword. The INSERT keyword specifies that a trigger will fire whenever you insert a row into a table, and the DELETE keyword specifies that a trigger will fire whenever you delete a row from a table. The UPDATE keyword specifies that a trigger will fire after you update any column in a table, but you can also use UPDATE OF to specify a column list when you want to fire a trigger after updating specific columns. If you use UPDATE OF to specify a column list, you can only use each column name once among all UPDATE triggers on that table.

The ON keyword specifies the name of the table you are creating the trigger on. (This is known as the trigger table.) The trigger table must be a permanent table in the database; you cannot create a trigger on a temporary table, a view, or a synonym. You can only specify a single trigger table for each trigger. As mentioned previously triggers do not have owners, but are associated with a table instead.

For Each Row Clause

old_name	Alias for referencing the values as they existed in the trigger table before the trigger action fired.
new_name	Alias for referencing the values as they exist in the trigger table after the trigger action fires.
search_condition	Conditions a row must meet for a trigger to fire.

The REFERENCING keyword specifies an alias for the OLD and NEW keywords. When you create a row trigger, you usually need to indicate in the trigger action whether you are referring to the value of a column before or after the trigger fires. You normally do this using the OLD and NEW keywords to refer to values from the trigger table, but in cases where you already have tables named OLD and NEW in your database, you can use the alias specified by the REFERENCING keyword in place of the OLD and NEW keywords.

The FOR EACH ROW keywords specify that a trigger will fire once for each row the trigger event modifies. Triggers defined using the FOR EACH ROW keyword will not fire if the statement that fires the trigger does not process any rows.

The WHEN keyword specifies that only rows that satisfying the search condition cause the trigger to fire. The WHEN clause is evaluated for each row the trigger event modifies. If the search condition is true, the trigger will fire for that row. If the search condition is false, the trigger will not fire. The result of the WHEN condition only affects the execution of the triggered action, it has no effect on the statement that fires the trigger.

For Each Statement Clause

The FOR EACH STATEMENT keywords specify that a trigger will fire once and only once for each statement that fires the trigger. Triggers defined using the FOR EACH STATEMENT keywords will fire even if the statement that fires the trigger does not process any rows.

The statement that the trigger executes when it fires is known as the trigger action. The trigger action may be an INSERT, UPDATE, DELETE, or EXECUTE PROCEDURE statement. You can only use built-in functions that have no argument, such as PI(), NOW(), or USER(), when specifying the trigger action. Stored procedures executed by a trigger cannot contain any transaction control statements such as COMMIT, ROLLBACK, or SAVEPOINT.

You can create multiple triggers for each trigger event on the trigger table using the trigger action time (BEFORE and AFTER keywords) in combination with the trigger type (FOR EACH ROW and FOR EACH STATEMENT keywords). For example, you can combine the trigger action time and the trigger type to create four triggers for the INSERT trigger event: BEFORE/FOR EACH STATEMENT, BEFORE/FOR EACH ROW, AFTER/FOR EACH ROW, AFTER/FOR EACH STATEMENT. You can do the same for the UPDATE and DELETE trigger events.

If you use UPDATE OF instead of UPDATE, you can create one trigger for each column in the table for each trigger action time/trigger type combination. This means that a table with four columns can have four UPDATE OF triggers for each of the combinations: BEFORE/FOR EACH STATEMENT, BEFORE/FOR EACH ROW, AFTER/FOR EACH ROW, AFTER/FOR EACH STATEMENT. If you use UPDATE OF to specify a trigger, you cannot use UPDATE to create a trigger on that table.

Trigger names must be unique for the table you are creating them on. Trigger names have a maximum length of eighteen characters, and may contain numbers, letters, the underscore character, and the symbols $ and #. The first character may not be a number.

EXAMPLES

The following example creates an UPDATE trigger named Trig1 on the Employees table that puts the values before and after the update into another table called NameChange. This trigger will fire before the trigger action for each row you update in the table, and will fire regardless of which column you update.

CREATE TRIGGER Trig1 BEFORE UPDATE ON Employees
                     FOR EACH ROW
                    (INSERT INTO NameChange
                          VALUES (OLD.FirstName, OLD.LastName,
                                  NEW.FirstName, NEW.LastName)

The following example creates an INSERT trigger named Trig2 on the Employees table that executes the stored procedure called SendMail when you insert a new row in the Employees table. This trigger will fire after the trigger action for each row you insert into the table. This example also uses the REFERENCING keyword to provide an alias for the OLD and NEW keywords.

CREATE TRIGGER Trig2 AFTER INSERT ON Employees
                     REFERENCING OLD AS pre NEW AS post
                     FOR EACH ROW
                    (EXECUTE PROCEDURE SendMail(pre.FirstName, 
                                                pre.LastName,
                                                WelcomeMessage)

The following example creates an UPDATE trigger named Trig3 on the Orders table that executes the stored procedure called LogTime when you update the Orders table. This trigger will fire before the trigger action, and will fire once and only once regardless of how many rows the trigger action updates.

CREATE TRIGGER Trig3 BEFORE UPDATE ON Orders
                     FOR EACH STATEMENT
                    (EXECUTE PROCEDURE LogTime)

RELATED COMMANDS

ALTER TRIGGER ENABLE

ALTER TRIGGER REPLACE

DROP TRIGGER

< CREATE TEXT INDEX | Contents | CREATE VIEW >