15.10 - CREATE TRIGGER and REPLACE TRIGGER - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Purpose

Creates or replaces a new trigger definition.

REPLACE TRIGGER changes the definition for a trigger without having to drop and recreate it.

See Temporal Table Support, B035-1182 for information about the temporal forms of CREATE TRIGGER and REPLACE TRIGGER.

Required Privileges

To create a trigger, you must have the following privileges:

  • CREATE TRIGGER on both of the following:
    • The database in which the trigger is created.
    • Either the subject table or its containing database.
  • SELECT on any column referenced in a WHEN clause or a triggered SQL statement subquery.
  • Depending on the triggered SQL statement, INSERT, UPDATE, or DELETE on the triggered SQL statement target table.
  • The privileges that would normally be required to execute the individual triggered SQL statements.

Creating or replacing a trigger does not grant trigger-related privileges to either the creator or the immediate owner of that trigger.

To replace a trigger you must have the following privileges:

  • DROP TRIGGER on the subject table or the database.

    The exception is when you use REPLACE TRIGGER when no target trigger exists and you instead create a new trigger.

    In that case, you need the CREATE TRIGGER privilege on both of the following:

    • The database in which the trigger is created.
    • Either the subject table or its containing database.
  • SELECT on any column referenced in a WHEN clause or a triggered SQL statement subquery.
  • Depending on the triggered SQL statement, INSERT, UPDATE, or DELETE on the triggered SQL statement target table.
  • The privileges that would normally be required to execute the individual triggered SQL statements.

The following privilege requirements apply to any user, other than the immediate owner, who performs a triggering statement:

  • You must have the privileges required to execute that triggering statement, and the immediate owner of the trigger must also hold all the privileges required to create the trigger.
  • If you have the required privileges for the triggering statement, but the immediate owner of the trigger no longer has the privileges required to perform the triggered action statements, then neither you nor any other user can run the triggering statement.

None.

Syntax