SQL Command and Function Reference - DBMaker Online / Manuals

ALTER TRIGGER REPLACE

Replaces an existing trigger with a new trigger.

SYNTAX

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

DESCRIPTION

The ALTER TRIGGER REPLACE command replaces an existing trigger with a new one. To execute the ALTER TRIGGER REPLACE 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 of the new trigger.

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 alter a trigger and replace it with a new one, you must specify the name of the trigger you want to replace, the new trigger action time (when a trigger should fire relative to the trigger event), the new trigger event (the event that causes the trigger to operate, or fire), the trigger table (the table you are creating the trigger on), the new trigger type (the type of trigger you want to fire), and the new trigger action (the action the database should take whenever the trigger fires). You must specify the same trigger table as in the original trigger.

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 replacing 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 this reason you must specify the same trigger table as the original trigger when you replace a trigger with a new one.

For Each Row Clause

old_name	Alias for referencing the values as they existed in the trigger table before the trigger action fires.
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 replace 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 satisfy the search condition will 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. When you replace a trigger with a new one, the new trigger cannot have the same combination as another trigger that already exists on the table.

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. When you replace a trigger with a new one, you cannot specify a column that has already been used in another UPDATE OF trigger.

EXAMPLES

The following example alters the trigger named Trig1 on the Employees table. This trigger was originally defined as a FOR EACH ROW trigger, and this command will replace it with a FOR EACH STATEMENT trigger.

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

The following example alters the trigger named Trig1 on the Employees table from the example above. This command will replace the UPDATE trigger event with an INSERT trigger event.

ALTER TRIGGER Trig2 REPLACE WITH
                    AFTER INSERT ON Employees
                    FOR EACH ROW
                   (INSERT INTO NameChange
                         VALUES (OLD.FirstName, OLD.LastName,
                                 NEW.FirstName, NEW.LastName)

The following example alters the trigger named Trig1 on the Employees table from the example above. This command will replace the INSERT statement with an EXECUTE PROCEDURE statement.

ALTER TRIGGER Trig2 REPLACE WITH
                    AFTER INSERT ON Employees
                    FOR EACH ROW
                   (EXECUTE PROCEDURE LogTime)

RELATED COMMANDS

ALTER TRIGGER ENABLE

CREATE TRIGGER

DROP TRIGGER

< ALTER TRIGGER ENABLE | Contents | BEGIN BACKUP >