create trigger

Creates one or more new triggers, or re-creates an existing trigger. A trigger is a type of stored procedure that is often used to enforce integrity constraints, executing automatically when a user attempts a specified data modification statement on a specified table.

Syntax

create [or replace] trigger [owner.]trigger_name
	on [owner.]table_name
	{for {insert , update} | instead of {insert, update, delete}}
	 [order integer]
     [as 
		[if update (column_name) 
			[{and | or} update (column_name)]...] 
			SQL_statements
		[if update (column_name) 
			[{and | or} update (column_name)]... 
			SQL_statements]...]

Parameters

Examples

Usage

See also sp_commonkey, sp_configure, sp_depends, sp_foreignkey, sp_help, sp_helptext, sp_primarykey, sp_rename, sp_spaceused in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Permissions on objects at trigger creation – when you create a trigger, the SAP ASE server makes no permission checks on objects such as tables or views that the trigger references. Therefore, you can successfully create a trigger, even though you do not have access to its objects. All permission checks occur when the trigger fires.

Permissions on objects at trigger execution – when the trigger executes, permission checks on its objects depend on whether the trigger and its objects are owned by the same user.
  • If the trigger and its objects are not owned by the same user, the user who caused the trigger to fire must have been granted direct access to the objects. For example, if the trigger performs a select from a table the user cannot access, the trigger execution fails. In addition, the data modification that caused the trigger to fire is rolled back.

  • If a trigger and its objects are owned by the same user, special rules apply. The user automatically has implicit permission to access the trigger’s objects, even though the user cannot access them directly. See the detailed description of the rules for implicit permissions in the System Administration Guide.

Permissions for instead ofand for triggers instead ofinstead of triggers have the same permission requirements as for triggers: to create a view with instead of triggers, permission for insert/update/delete for the view, not the underlying tables, must be granted to the user.

Any user who impersonates the trigger owner through an alias or setuser cannot replace the trigger.

The following describes permission checks for create trigger that differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be the table owner and the create trigger privilege must not have been revoked to create a trigger. To create a trigger for another user's table, you must have the create any trigger privilege.

You must be the trigger owner to replace the trigger.

Disabled

With granular permissions disabled, only a system security officer can grant or revoke permissions to create triggers. A user with sa_role has implicit permission to create a trigger on any user table. Users can create triggers only on tables that they own.

You must be the trigger owner to replace the trigger.

The system security officer may revoke user permission to create triggers. Revoking permission to create triggers affects only the database in which the systems security officer issues the revoke command. Permission to run the create trigger command is restored to the users whose permission was revoked when the system security officer explicitly grants them create trigger privilege.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

12

Audit option

create

Command or access audited

create trigger

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

  • or replace – for create or replace
Related reference
alter table
create procedure
drop trigger
rollback trigger
set
create table
select
create database
create default
create index
create rule
create view
alter database
truncate table
grant
revoke
update statistics
load database
load transaction
disk init
disk refit
disk reinit
disk remirror
disk unmirror