Creating a Table Trigger

Create a new trigger on a table in the system store.

Prerequisites
Database Version Table Trigger Privileges
SAP Sybase IQ 15.3 and 15.4 Requires one of:
  • DBA authority.
  • RESOURCE authority, along with one of:
    • ALTER permission on the underlying table of the trigger.
    • You own the underlying table of the trigger.
SAP Sybase IQ 16.0 Requires one of:
  • CREATE ANY TRIGGER system privilege.
  • CREATE ANY OBJECT system privilege.
Also requires one of:
  • ALTER ANY OBJECT system privilege.
  • ALTER ANY TABLE system privilege.
  • ALTER permission on the underlying table of the trigger.
  • You own the underlying table of the trigger.
Task
  1. In the Perspective Resources view, select the resource and select Resource > Administration Console.
  2. In the left pane, select IQ Servers > Schema Objects > Tables.
  3. Select Tables or Proxy Tables.
  4. Select a table or proxy table from the right pane and either:
    • Click the arrow to the right of the name and select Properties, or
    • From the Administration Console menu bar, select Resource > Properties.
    The Table or Proxy Table Properties view appears.
  5. In the left pane, select Triggers.
    Note: Triggers does not appear when the selected table is not a system store table.
  6. In the right pane, click New.
    The Create Trigger Wizard appears.
  7. On the Welcome page, enter a unique name for the new trigger.
  8. Click Next.
  9. On the Events page, select one or more events to cause the trigger to fire.
    Note: When Update with Columns is selected, all other events become disabled.
    1. (Update with Columns only) Select one or more columns which cause the trigger to fire when updated.
  10. Click Next.
  11. On the Level page, indicate whether the trigger is to fire at the row or statement level.
  12. Click Next.
  13. On the Timing page, specify:
    Option Description
    When do you want the trigger to be fired? Specify when the trigger is to fire. Availability of timing options is determined by events and levels specified.
    • Before the event – Option disabled when Level = Statement-level
    • Instead of the event – Option disabled when Event = Update of Columns.
    • After the event
    • On a SQL Remote conflict – Option available when Level = Row-level or Event = Update of Columns only
    What order in the firing list do you want for this trigger? Assign a firing order to the trigger to ensure that triggers of the same type that fire at the same time fire in a specific order.
    Note: This option is unavailable when Instead of the event is selected.
  14. Click Next.
  15. On the SQL page, enter the statement to be executed when the trigger fires. Triggers do not fire after the execution of LOAD TABLE, TRUNCATE, or WRITETEXT statements.
    The statement window provides a template on which to build the trigger statement. The parameters displayed are based on the options selected in the preceding pages. Once you have made any changes in the statement window, if you return to any of the preceding pages and change any option, when you return to the SQL page, the contents of the statement window is overwritten with the base template; any customizations are lost.
  16. Click Next.
  17. On the Comment page, enter an optional comment.
  18. Click Finish.
    Note: Unlike other wizards launched from the properties view, changes to triggers take effect immediately. You do not need to click OK or Apply in the properties view to upload the change to the database.
Parent topic: Table Triggers
Related tasks
Authenticating a Login Account for a Managed Resource