What is a Trigger:
In ASE, trigger is a piece of SQL code that gets executed automatically when a specific action on a specific table happens.
What are the actions that fire the trigger?
A Trigger can be fired on 3 actions: Insert, Update or Delete or any combination of them.
How many Triggers we can have per table?
Prior to ASE 16.0 you could have one code running for Inserts, Updates or Deletes or for partial or total group. I.e. one trigger for update and delete or for 3 actions altogether.
Starting from ASE16.0 you can have multiple triggers per action for every table and you can also specify the order of execution. This is a helpful addition if you are using triggers for multiple purposes (e.g. Auditing and referential integrity) then you can have two triggers for insert, one to enforce the referential integrity and one to perform the auditing.
Can a Trigger capture the before and after values of the firing action?
Yes. every Trigger has internal virtual tables Deleted and Inserted that contain one row in Inserted for each insert, one row in Deleted for every deleted row and one row in both Inserted and Updated table for every Updated row (Old row as deleted and new row as inserted).
You can select from these two tables from inside the trigger only and the data will be row specific only.
Can I Temporarily Disable the Trigger?
Yes. You can disable any trigger and re-enable it back later using alter table command.
alter table [database_name.[owner_name].]table_name
{enable | disable} trigger [trigger_name]
You can check the trigger status using:
sp_help trigger_name
Also you can check all the disabled triggers inside the database using the following SQL (status2 values not documented):
select name ,
case
when sysstat2 & 1048576 /*(0x100000)*/ != 0 then object_name (instrig)+" Insert trigger disabled"
when sysstat2 & 2097152 /*(0x200000)*/ != 0 then object_name(deltrig)+" Delete trigger disabled"
when sysstat2 & 4194304 /*(0x400000)*/ != 0 then object_name(updtrig)+" Update trigger disabled"
end
from sysobjects
where type = "U" and (sysstat2 & 1048576 /*(0x100000)*/ != 0 OR
sysstat2 & 2097152 /*(0x200000)*/ != 0 OR
sysstat2 & 4194304 /*(0x400000)*/ != 0)
Disabling the trigger can be useful for testing purposes or during batch processing to reduce the impact of the trigger during an operation where there's no Auditing required.
Trigger Rollbacks:
ASE deals with the transaction of the action that fired the trigger – insert/update/delete – and the trigger code as one big transaction, so a simple rollback inside the trigger will not only rollback the trigger and its action but also the originating WHOLE transaction.
Sybase provides an option to change that behavior if you want to roll back only the trigger (and the statement that fired it) via the special roll back command:
rollback trigger
If the trigger that issues rollback trigger is nested within other triggers, the SAP ASE server rolls back all work done in these triggers up to and including the update that caused the first trigger to fire.
Trigger Self-recursion:
By default, a trigger does not call itself recursively. That is, an update trigger does not call itself in response to a second update to the same table within the trigger. If an update trigger on one column of a table results in an update to another column, the update trigger fires only once. However, you can turn on the self_recursion option of the set command to allow triggers to call themselves recursively. The "allow nested triggers" configuration variable must also be enabled for self-recursion to occur.
Triggers and performance
In terms of performance, trigger overhead is usually very low. The time involved in running a trigger is spent mostly in referencing other tables, which may be either in memory or on the database device.
The deleted and inserted trigger test tables are always in active memory. The location of other tables referenced by the trigger determines the amount of time the operation takes.
Sample Syntax:
create trigger junktrig
on junk
for insert
as
if update(a) and update(b)
print "FIRING"
/*"if update" is true for both columns.
The trigger is activated.*/
insert junk (a, b) values (1, 2)
/*"if update" is true for both columns.
The trigger is activated.*/
insert junk values (1, 2)
/*Explicit NULL: "if update" is true for both
columns. The trigger is activated.*/
insert junk values (NULL, 2)
/* If default exists on column a,
"if update" is true for either column.
The trigger is activated.*/
insert junk (b) values (2)
/* If no default exists on column a,
"if update" is not true for column a.
The trigger is not activated.*/
insert junk (b) values (2)
For deeper understanding of Triggers in SAP Sybase ASE, Please refer to the documentation .
