Changing Triggers - Firebird

Firebird 1.0.x offers just one way to change triggers using DDL statements and Firebird 1.5 adds another:

  • ALTER TRIGGER changes the definition of an existing trigger module while preserving its dependencies on other objects. It can be used with minimal disturbance to deactivate a trigger.
  • CREATE OR ALTER TRIGGER (available from v.1.5 onward) creates the trigger module if it does not exist and works exactly as CREATE TRIGGER does. Otherwise, ALTER rules apply and dependencies are preserved. Either operation will fail with an exception if any change is attempted that would break a dependency.

Syntax for Changing Triggers

The syntax pattern is

{ALTER TRIGGER name} | {CREATE OR ALTER TRIGGER name FOR {table | view}
[ACTIVE | INACTIVE]
[{BEFORE | AFTER} {DELETE | INSERT | UPDATE}]
[POSITION
number]
AS <trigger_body>;

ALTER TRIGGER

The FOR name clause that is used in CREATE TRIGGER is omitted. ALTER TRIGGER cannot be used to change the table with which the trigger is associated.

Changing Only the Header

When you use it to change only a trigger header, ALTER TRIGGER requires at least one altered attribute after the trigger name. Any header attribute omitted from the statement remains unchanged.

The following statement deactivates the trigger SAVE_SALARY_CHANGE:

ALTER TRIGGER SAVE_SALARY_CHANGE INACTIVE;

If the phase indicator (BEFORE or AFTER) is altered, then the event (UPDATE, INSERT, or DELETE) must also be specified. For example, the following statement reactivates the trigger VERIFY_FUNDS and specifies that it fire before an update instead of after:

ALTER TRIGGER SAVE_SALARY_CHANGE
ACTIVE BEFORE UPDATE;

Changing the Body

Any change to the trigger body causes the new body definition to replace the old definition. ALTER TRIGGER need not contain any header information other than the trigger’s name.

For example, the following statement modifies the trigger SET_CUST_NO that was created with this definition:

CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
BEFORE INSERT
AS
BEGIN
IF (NEW.CUST_NO IS NULL) THEN
NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
END^

We will alter the trigger to have it insert a row into a new table, NEW_CUSTOMERS, each time a new row is inserted into the CUSTOMER table:

SET TERM ^;
ALTER TRIGGER SET_CUST_NO
BEFORE INSERT AS
BEGIN
IF (NEW.CUST_NO IS NULL) THEN
NEW.CUST_NO = GEN_ID(CUST_NO_GEN, 1);
INSERT INTO NEW_CUSTOMERS(NEW.CUST_NO, CURRENT_DATE)
END ^
SET TERM ;^

CREATE OR ALTER TRIGGER

New in v.1.5, this tolerant syntax creates a new trigger if one with the supplied name is not found, or alters an existing trigger of that name. Simply edit the original CREATE definition as required, inserting the keywords OR ALTER.

“Object Is in Use” Error

As with stored procedures, committing the change will throw the notorious error if any user is currently using the procedure or another object that depends on it. In any event, the new version of the trigger will not be immediately available on Superserver if the old version is still in the cache. All users must log out and, when they log in again, they will see the new version.

On Classic server, the new version will be available to the next client that logs in.

Inactive/Active

In v.1.5 and later, performing ALTER TRIGGER . .. INACTIVE | ACTIVE does not usually invoke the “Object in use” error unless an existing transaction has a table lock. The change will not affect transactions that already have the table in their purview. However, it should be visible to the next transaction that requests a state change on the table.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics