Creates a new trigger on a given table.

Syntax

---

CREATE TRIGGER <Name> <ActionTime> <Type> ON <TableName>
[AT <ExecutionPos>]
[WHEN <Condition>]
<BodyDefinition>
[DESCRIPTION <Description>]

<ActionTime>=BEFORE|AFTER|ERROR

<Type>=INSERT|UPDATE [OF <UpdateColumns>]|DELETE|LOAD UPDATE|ALL

<UpdateColumns>=

<ColumnName> [,<ColumnName>]

<BodyDefinition> =

BEGIN
   [<Declaration>;]
   [<Declaration>;]
   [<Statement>;]
   [<Statement>;]
[EXCEPTION]
   [<Statement>;]
END

Usage

---

Use this statement to create a new trigger on a table. Triggers can be created to respond to any INSERT, UPDATE, DELETE, or LOAD UPDATE of a row. The AT clause can be used to specify the position (1-based) of the new trigger in relation to any existing triggers on the same table. You can use the WHEN condition to restrict when the trigger will fire as well as the OF clause to restrict the trigger to firing only when certain columns are updated.

OLDROW/NEWROW Row Values

You may refer to the special row identifiers OLDROW and NEWROW anywhere within the WHEN condition or the trigger body itself. They identify the row being inserted, updated, or deleted in the state prior to the action (OLDROW) and after the action (NEWROW).


For any type of trigger, you can use the LOADINGUPDATES function to determine whether the trigger is executing during the execution of a LOAD UPDATES statement. This is useful for situations where you only want triggers to execute when loading updates, or want to conditionally execute different code depending upon whether the operation is due to a LOAD UPDATES statement execution.

ERROR Triggers

Error triggers are a special kind of trigger that can be defined for insert, update, or delete operations and are called whenever an error occurs during these operations. Normally, the ERRORCODE and ERRORMSG functions are accessible only from within EXCEPTION blocks. However, they are also accessible from anywhere within an error trigger. In addition, the RETRY statement is provided for allowing the trigger to attempt to correct the exception and retry the operation that originally caused the error.

Universal Triggers

Starting in 2.04, you may define a universal trigger using the ALL keyword instead of a specific INSERT, UPDATE, DELETE, or LOAD UPDATE trigger type. This will cause the trigger to be fired for all operations, and you can use the OPERATION function to determine the current operation that caused the trigger to be fired. In the case of a LOAD UPDATE trigger, the current operation is always the type of operation for the update that the LOAD UPDATES statement is currently trying to load.

LOAD UPDATE Triggers

Starting in 2.05, you can also create LOAD UPDATE triggers that are fired for each update being loaded during the execution of the LOAD UPDATES statement. This is useful for being able to respond to update load errors due to constraint violations or missing rows, as well as controlling the update loading process itself by choosing which updates should or should not be loaded (see next section on aborting an operation). Please see the Replication topic for more information on loading updates for a database.

LOAD UPDATE triggers occur before any triggers for the actual update operation occurring. The following shows the order in which the operations that make up the loading of an update occur:

• If the update being loaded is not an INSERT, then the primary key values for the UPDATE or DELETE are loaded.



• The BEFORE LOAD UPDATE triggers are executed, giving you the chance to modify the primary key values in the OLDROW column values before ElevateDB searches for the row in an UPDATE or DELETE operation.



• For UPDATE or DELETE operations, ElevateDB searches for the row using the primary key values.



• The INSERT, UPDATE, or DELETE operation is performed:
  
  If the update being loaded is an INSERT, then the BEFORE INSERT triggers, INSERT operation, AFTER INSERT triggers, or ERROR INSERT triggers (if any errors occur) are executed.
  
  If the update being loaded is an UPDATE, then the BEFORE UPDATE triggers, UPDATE operation, AFTER UPDATE triggers, or ERROR UPDATE triggers (if any errors occur) are executed.
  
  If the update being loaded is a DELETE, then the BEFORE DELETE triggers, DELETE operation, AFTER DELETE triggers, or ERROR DELETE triggers (if any errors occur) are executed.



• The AFTER LOAD UPDATE triggers are executed, with the NEWROW column values representing the column values after the operation has been executed.



• If any errors occur during this entire sequence of operations, then the ERROR LOAD UPDATE triggers are executed.

The OLDROW and NEWROW row values have a specific usage when accessed from within LOAD UPDATE triggers, depending upon the operation being performed. As mentioned above, you can use the OPERATION function to determine the actual operation being performed.


Using a Trigger to Abort an Operation

Starting in 2.05, you can use the ABORT statement to abort any INSERT, UPDATE, DELETE, or LOAD UPDATE operation. Aborting an operation sets the aborted flag for the current operation and, after the current trigger is done executing, will cause the operation and any subsequent triggers to be silently ignored. For example, calling ABORT in a BEFORE LOAD trigger will cause the current load operation to stop and the LOAD UPDATES execution to continue on the next update to be loaded, if any more updates are present in the incoming update file.


Examples

---

-- This trigger calls the external
-- SendMail procedure with which group to
-- send the email to along with the new
-- value of the Notes column for the customer
-- being updated

CREATE TRIGGER "NotesUpdate" AFTER UPDATE OF "Notes"
ON "Customer"
BEGIN
   CALL SendEmail('CustomerReps',NEWROW.Notes);
END

-- This trigger logs any insert errors that
-- occur during a LOAD UPDATES for
-- the Customer table into a table called
-- LoadErrors

CREATE TRIGGER "LogInsertError" ERROR INSERT ON "customer"
WHEN LOADINGUPDATES()
BEGIN
   DECLARE ErrorData VARCHAR DEFAULT '';

   SET ErrorData = 'Cust #: ' + CAST(NEWROW.CustNo AS VARCHAR);
   SET ErrorData = ErrorData + 'Name: ' + NEWROW.Company;
   SET ErrorData = ErrorData + 'Error #: ' + CAST(ERRORCODE() AS VARCHAR);
   SET ErrorData = ErrorData + 'Error Msg: ' + ERRORMSG();

   EXECUTE IMMEDIATE 'INSERT INTO LoadErrors (''Customer'',''INSERT'',
                                              ''' + ErrorData + '''';
END

-- This trigger updates any new row with
-- a timestamp of when the row was inserted
-- into the Customer table.  The AT clause
-- is used to ensure that this trigger always
-- fires first before any other triggers

CREATE TRIGGER "SetTimeStamp" BEFORE INSERT ON "customer"
AT 1
BEGIN
   SET NEWROW.CreatedOn = CURRENT_TIMESTAMP();
END

-- This trigger examines the primary key
-- values for an update being loaded into
-- the Customer table.  If the SiteID column
-- value for the update does not match the
-- SiteID column in the System table in the
-- same database, then the loading of the update
-- is aborted using the ABORT statement.
-- NOTE: the column being filtered on, in this case
-- the SiteID column, must be part of the primary
-- key in order for it to be non-NULL in the
-- OLDROW column values for UPDATE and DELETE
-- operations.

CREATE TRIGGER "FilterUpdates" BEFORE LOAD UPDATE ON "customer"
BEGIN
   DECLARE SiteID INTEGER DEFAULT 0;

   EXECUTE IMMEDIATE 'SELECT SiteID INTO ? FROM System' USING SiteID;

   IF OLDROW.SiteID <> SiteID THEN
      ABORT;
   END IF;
END

Required Privileges

---

The current user must be granted the CREATE privilege on the specified table in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: