create trigger for or replace

The or replace clause allows you to replace a trigger's definition using create trigger.

In versions earlier than SAP ASE 16.0, consecutive create trigger commands dropped the old trigger and replaced it with a new trigger definition. However, the auditing options for the trigger were also dropped. Using the optional or replace clause, the definition is replaced, and auditing options are preserved.

Syntax

Changes are in bold.

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]...]

Parameter Changes for create or replace trigger

create – creates a trigger if one does not already exist. 

  • In SAP ASE version 16.0 and later, when there are multiple triggers, specifying create without or replace raises an error if the trigger name is same. If you specify a different trigger name, a new trigger is created and the old trigger remains. Also see, Multiple Triggers

  • In versions of SAP ASE earlier than 16.0, if an existing trigger was replaced with the new trigger definition by specifying create without or replace, the name of the new trigger did not need to be same as the name of the old trigger name.

  • or replace – re-creates an existing trigger. Use this clause to change the definition of a trigger. When specifying or replace, auditing options on the trigger are not dropped. If there is no existing trigger with the name you enter, a new one is created and the old trigger remains. This is in conjunction with multiple triggers .  
  • trigger_name – the name of the trigger is not changed when the trigger definition is replaced. The name of the new trigger definition must match the old name to be replaced. If the trigger name differs from any existing trigger, a new trigger is created and the old trigger is not dropped .  
  • table_name – you cannot change the name of the table when a trigger is replaced. If an existing trigger is modified to associate the trigger with another table, then an error is raised indicating that the trigger already exists on another table and cannot be replaced.
  • for | instead of – you cannot change an "instead of" trigger to a "for" trigger, and vice versa.
  • insert,update,delete – you can change these actions when you use the or replace clause. For example, if the old trigger definition specifies all clauses, the replacement definition can specify all clauses, or a combination of the actions.
  • SQL_statements – you can change trigger conditions and actions when the trigger definition is replaced.    
  • if update – you can drop or add the if update and change the column name referenced by this clause.  
  • order integer – the order of the trigger firing can also be changed when the trigger definition is replaced.

Example

This example creates a trigger that prints a message when anyone tries to insert or update data in the titles table: 

create trigger reminder    
on titles    
for insert, update as    
print "Don't forget to print a report for accounting." 

select object_id("reminder")      
-----------     
1312004674

The next command changes the message of the printed trigger when anyone tries to update data in the titles table using the or replace clause:

create or replace trigger reminder    
on titles    
for update as    
print "Don't forget to give a report to accounting." 

select object_id("reminder")      
-----------     
1312004674

Permission Changes for create or replace trigger

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

Changes for replacing a trigger are in bold.

Granular permissions enabled With granular permissions enabled, you must be the table owner and the create trigger privilege must not have been revoked. You must have the create any trigger privilege to run create trigger on another user's table. You must be the trigger owner to replace the trigger.
Granular permissions disabled

With granular permissions disabled:

Only a system security officer can grant or revoke permissions to create triggers. The database owner has implicit permission to create a trigger on any user table. Users can create triggers only on tables that they own.

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 permission.

You must be the trigger owner to replace the trigger.

Auditing Changes for create or replace trigger

Changes are in bold.

Event Audit Option Command or access audited Information in extrainfo
12 create create trigger
  • Roles – current active roles
  • Keywords or options – NULL
  • Previous value – NULL
  • Other information – NULL
  • Current value – NULL
  • Proxy information – original login name, if set proxy is in effect
  • or replace – for create or replace
Parent topic: Commands