Restrictions for instead of

Restrictions for using instead of triggers.

  • If a trigger references table names, column names, or view names that are not valid identifiers, you must set quoted_identifier on before the create trigger command, and enclose each such name in double quotation marks. The quoted_identifier option does not need to be on when the trigger fires; bracketed identifiers also work.

  • Using the set cursor rows command with client cursors, cursors declared through Open Client calls, or Embedded SQL™, may prevent positioned delete and update from firing an instead of trigger. A positioned update statement is a SQL update statement that contains the where current of <cursorname> clause to update only the row upon which the cursor, <cursorname>, is currently positioned.

  • Joins are not allowed in searched delete and update statements that would fire an instead of trigger.

  • positioned delete and update on cursors defined with joins does not fire an instead of trigger.

    A positioned delete (or positioned update) is a SQL delete (or update) statement containing a where current of <cursorname> clause to delete (or update) only the row upon which the cursor, <cursorname>, is currently positioned.

  • For positioned delete and update statements that fire an instead of trigger, the instead of trigger must exist when the cursor is declared.