Now that you have migrated to the SAP ASE Database Server, this is the perfect time to make sure we establish standards such as naming conventions and coding standards. This discussion on best practices for triggers is just one example of a coding
template that may be used in your building of the ASE Database Server trigger programs.
A trigger is a stored procedure that goes into effect (we say "fires") when you insert, delete, or update data in a table. Triggers are automatic; the execution of the code inside a trigger is executed upon an insert, update, delete command on the trigger's associated table. A trigger is specific to one or more of the data modification operations (update, insert, and delete), and is executed once for each SQL statement (this is true if we affect 1 row or 1000 rows by the single I/U/D statement).
We use triggers for:
- cascading changes through related tables in the database
- roll back changes that would violate referential integrity beyond the check constraints syntax
- enforce restrictions that are much more complex than those that are defined with constraints or rules.
- anything you can think of.....If we can code it in SQL then we can put it into a trigger**.
A trigger “fires” only after the data modification statement has completed and Adaptive Server has checked for any datatype, rule, or integrity constraint violation. The trigger and the statement that fires it are treated as a single transaction that can be rolled back from within the trigger. If Adaptive Server detects a severe error, the entire transaction is rolled back. While the trigger fires after the system validity checks have been completed, we do have a last opportunity to either proceed as normal or to roll back the trigger and the original statement causing the trigger to fire by issuing a rollback trigger command or roll back to the entire outer transaction using a rollback transaction command. The template listed below features the rollback trigger error checking. While both rollbacks are available to be used I believe it is better to use the rollback trigger command to make our code is more modular and portable.
Specific to our program inside a trigger there are two tables (really memory structures that mimic the fields of the base table):
inserted - a table holding entire records from an insert command or the after fields from an update command.
deleted - a table holding entire records from a delete command or the previous fields from an update command.
Depending upon the row counts in these two tables, we can determine what the original command was and how many rows are affected.
The following example of a trigger is a single solution for inserts, updates and deletes and is responsible for 1 to 100+ rows being processed in the same manner. Hopefully the comments I have provided in the code explain why we are doing certain commands. The BOLD code is where you would introduce your own logic into the template.
-- Note the naming convention we have chosen to include the table name and this one piece of code
-- is for inserts, updates and deletes
-- File: <TABLENAME>_I/U/D.trg
-- Generic structure for a trigger
-- no business rules to be inserted directly into triggers
--
create trigger <TABLENAME>_IUD
on <TABLENAME>
for update, insert, delete
as
-- this trigger allows multiple row processing
declare @mod_type char(1), @new_rows int, @old_rows int
declare @maint_check int, @name_place char(30), @row_num int
declare @mod char(1), @ret_status int
-- no rows to process, lets get out
select @row_num = @@rowcount
if @row_num = 0
return
-- These next lines are for those environments that use Replication Server
-- if the replication maintenance user is involved then no more need to process
-- as all checks are done at the primary site
select @name_place = suser_name()
select @main_check = patindex('%maint%',@name_place, using characters)
-- if this trigger is fired by the replication maintenance user then terminate
if @maint_check >
0
return
-- end of Replication Server check
-- your RI rules can go here!!!!!
select @new_rows = count(*) from inserted
select @old_rows = count(*) from deleted
-- get the type of transaction Insert, Update, Delete
if @new_rows = @old_rows select @mod = 'U'
if @new_rows = 0 select @mod = 'D'
if @old_rows = 0 select @mod = 'I'
-- one row to process
if @row_num = 1
-- any application logic is contained in a stored procedure to make it available to other programs
exec @ret_status = <your APPLICATION LOGIC PROCEDURE here !!!!!>
-- error checking takes care of its own. Our calling program needs to check its status for -999 and
-- issue the correct response in the main line program.
if (@ret_status = -999)
begin
rollback trigger with raiserrror 25000 'trigger for <TABLENAME> rolled back after status of -999 encountered.'
end
-- multiple rows to process
if @row_num > 1
begin
-- open up cursors for row by row processing
declare new_cursor cursor for select <FIELDS> from inserted
declare old_cursor cursor for select <FIELDS> from deleted
open new_cursor
open old_cursor
if @mod = 'U'
begin
fetch old_cursor into <VARIBLES TO HOLD FIELDS>
fetch new_cursor into <VARIBLES TO HOLD FIELDS>
end
IF @mod = 'I'
fetch new_cursor into <VARIBLES TO HOLD FIELDS>
IF @mod = 'D'
fetch old_cursor into <VARIBLES TO HOLD FIELDS>
while @@sqlstatus != 2
begin
exec @ret_status = <your APPLICATION LOGIC PROCEDURE here !!!!!>
-- error checking takes care of its own. Our calling program needs to check its status for -999 and
-- issue the correct response in the main line program.
if (@ret_status = -999)
begin
rollback trigger with raiserrror 25000 'trigger for <TABLENAME> rolled back after status of -999 encountered.'
end
if @mod = 'U'
begin
fetch old_cursor into <VARIBLES TO HOLD FIELDS>
fetch new_cursor into <VARIBLES TO HOLD FIELDS>
end
IF @mod = 'I'
fetch new_cursor into <VARIBLES TO HOLD FIELDS>
IF @mod = 'D'
fetch old_cursor into <VARIBLES TO HOLD FIELDS>
end
end
/* close and deallocate happens automatically
*/
return
go
**There are some cases where triggers do have restrictions. These are:
- You cannot create a trigger on a view or on a temporary table, though triggers can reference views or temporary tables.
- The writetext (for text fields) statement does not activate insert or update triggers.
- Truncate table statements do not fire triggers.
- You cannot create a trigger or build an index or a view on a temporary object (@object).
- You cannot create triggers on system (SAP Sybase internal) tables.
- You cannot use triggers that select from a text column or an image column of the inserted or deleted table.
Next blog I will focus on writing standards for SAP ASE Stored Procedures and introduce a modular template. If you have any questions on how to get started SAP has several Healthchecks for the ASE Server to get you started on the right track. Contact myself or the SAP Global Database Center of Excellence and we would be happy to help..
Help is click away......
Follow the “Database Services Content Library” to access the entire series of Database Services Blogs and join the conversation on Twitter @SAPServices. Learn more about SAP Database Services here
http://www.sap.com/services-and-support/data-and-technology/database-services.epx
Some of the reference materials for this talk were found in the User Guide for the ASE Server version 15.7
