Sybase triggers protect your data

by Guest Contributor | May 28, 2002 7:00:00 AM

Tags: Guest Contributor

Takeaway: Triggers are used to preserve data integrity by checking on or changing data in a consistent manner. Sybase provides various mechanisms for utilizing the power of triggers.

Automating routine tasks is the hallmark of a great application. This automation allows the developer to tackle more important issues. Triggers provide automation to database developers. This article introduces the concepts associated with using triggers in Sybase.

Sybase triggers are your data enforcers
A database trigger is a set of SQL statements that automatically "fires off" an action simultaneously with a specific operation, such as changing data in a table. A trigger consists of an event (an INSERT, DELETE, or UPDATE statement issued against an associated table) and an action (the related procedure). Triggers are used to preserve data integrity by checking on or changing data in a consistent manner.

Triggers are most often used to implement referential integrity or complex rules, or for keeping duplicate, summarized, or aggregated values in sync with detail records. While defaults bound to a column may contain only one value, a trigger can enforce a multitude of default values conditionally based on other data in the same or another table.

Triggers can enforce complex referential integrity (RI) where a lookup is required in another table, or even another column in the same table. They can prevent the modification of primary/foreign keys or even cascade the modifications to other tables. Many also consider storing summary values (or most other derivative values) in another table in real-time a justifiable use of triggers. Triggers can enforce complex business rules or quality assurance where you want to ensure that an updated value doesn’t deviate too far from the original value. Triggers are preferred over integrity constraints for another important reason: integrity constraints don’t facilitate conditional transaction management.

One trigger can have many uses
A trigger may be fired for any given data modification language (DML) SQL statement. These statements include INSERT, UPDATE, and DELETE, and each event may have its own trigger. It’s also possible to use the same trigger for more than one DML event by specifying what actions you wish the trigger to fire on in the CREATE statement.

Many developers believe the statement fires once for every affected row. Actually, a trigger fires only once for the DML statement that fired it. It’s a common misconception that a trigger is not fired if no rows are affected. Actually, the trigger fires even if no rows are affected. This can occur if the “where” clause is unsatisfied. The only situation in which a trigger will not fire is when a table constraint (defined as part of the CREATE statement which created the table) disallows the modification. Also, Delete triggers will not fire on a truncate table statement, and Insert triggers will not fire as a result of the Writetext command.

Trigger creation is simple and flexible
The syntax for creating a Sybase trigger is as follows:
create trigger [owner.]trigger_name
on [owner.]table_name
{for {insert , update , delete}
as SQL_statements

Or, using the if update clause:
create trigger [owner.]trigger_name
on [owner.]table_name
for {insert , update}
as
[if update (column_name )
[{and | or} update (column_name )]...]
SQL_statements
[if update (column_name )
[{and | or} update (column_name )]...
SQL_statements ]...

To drop a trigger, the syntax is as follows:
drop trigger <trigger_name>

You can temporarily disable a trigger if you are loading a table from a previous dump and need to speed up the process. This capability was introduced in Sybase Adaptive Server 12.0. To disable or re-enable a trigger, the syntax is as follows:
alter table [database_name.[owner_name].]table_name
{enable | disable} trigger [trigger_name]

To keep things simple, we’ll create a trigger whose only purpose is to prevent deletions and insertions in a table. The syntax is as follows:
create trigger publishers_IDtg
on publishers
for insert, delete
as
begin
print "You cannot insert or delete any records!"
rollback transaction
return
end

This trigger will fire whenever a DELETE or INSERT statement executes on the publishers table. This also includes situations where another trigger may have inserted or deleted records into this table. Rollback Transaction reverts any modifications made to the database from either the explicit or implied transaction that contained the SQL DELETE statement that caused the trigger to fire.

Now, let’s create a trigger that uses if update and whose job it is simply to disallow modifications to a primary key.
create trigger publishers_Utg
on publishers
for update
as
if update( pub_id )
begin
print "You cannot Update the primary key!"
rollback transaction
return
end
 

Triggers access the inserted and deleted tables
During its execution, a trigger has access to two special tables. These tables are exactly the same in structure as the trigger table. They are always referred to as “inserted” and “deleted.” A trigger can access these tables to determine what modifications have occurred, but the trigger can’t modify these tables. Note that updates to table rows result in deletions followed by insertions. So, if records appear in both the deleted and inserted tables, the trigger knows an update was performed.

Rollback Transaction and Rollback Trigger prevent unwanted modifications
There are two commands that will explicitly roll back all modifications made by the SQL statement that fired a trigger: Rollback Transaction and Rollback Trigger.

Rollback Transaction
Use Rollback Transaction to prevent the modifications that fired the trigger, to roll back all changes back to the outermost begin transaction and to abort the rest. This is the method used most often; it ensures that all modifications associated with the outermost transaction, or batch are rolled back.

Rollback Trigger [with raiserror]
Rollback Trigger is used to prevent the modifications that fired the trigger. It does allow any other transaction modifications or batch to be committed at the end. This method of rolling back is trickier to use and should be debugged thoroughly, as it may allow partial modifications and result in a loss of data integrity.

Let’s consider an example of this approach:
begin tran
insert into table_a (column_a) values (“11111”)
insert into table_a (column_a) values (“22222”)
insert into table_a (column_a) values (“33333”)
commit trans
end trans

If the Insert trigger on table_a issued a Rollback trigger on any firing of the Insert triggers in the above example, the other Inserts would not be affected and would subsequently be committed.

If the Insert trigger on table_a issued a rollback transaction on any firing of the Insert triggers in the above example, the already executed Inserts would be rolled back, and any remaining Inserts would never execute at all.

Generate lists to identify and view trigger source text
You can generate a list of triggers currently defined in the database using the following SELECT statement:
select *
from sysobjects
where type = "TR"

You can view the source code of the trigger as follows:
sp_helptext <trigger_name>

As long as sp_hidetext hasn’t been used to encrypt the trigger text and the DBA has not disabled the ability to view the syscomments.text column, you should see the text used to create the specified trigger.

If you wish to know what tables and views a trigger references, there is always the sp_depends stored procedure.
sp_depends <trigger_name>

Trigger rules and limitations prevent unauthorized changes
Here is a list of trigger rules and limitations.

• Only the owner of a table can create or drop a trigger on that table.
• Unlike views and stored procedures, permissions apply the same way inside a trigger as they do in a normal SELECT statement. Thus, if a user doesn’t have permission to update table_b and he updates table_a and fires trigger_a, which tries to update table_b, the trigger will fail and the modifications will fail.
• In a trigger you may reference up to 192 other database objects like tables, views, etc.

What isn't allowed in a trigger?
Here is a list of rules for what is not allowed in a trigger:

• Create and Drop commands are not allowed in any form. However, temp tables may be called in a stored procedure that is called by the trigger.
• Alter table/database is not allowed.
• Grant and Revoke are both disallowed.
• Select Into isn’t allowed.
• Truncate Table is prohibited.
• Update Statistics isn’t allowed.
• Reconfigure is not allowed.
• Load Database/Transaction isn't allowed.
• Disk Init/Mirror/Reinit/Refit/Remirror/Unmirror are all disallowed.

Trigger naming conventions are important
Any good programmer knows the importance of naming conventions. Triggers are no exception. Trigger names are limited to 30 characters. My personal preference is to use the full table name with the suffixes in Table A.
Table A

Trigger Type	Table Name & Suffix
Insert	employee_Itg
Update	employee_Utg
Delete	employee_Dtg
Update & Delete	employee_UDtg
Update & Delete & Insert	employee_UDItg
* Assuming the table name is employee

Trigger naming

Using triggers is easier than you think
This is by no means a definitive “end-all” to triggers. You’d need further discussion of these topics and others not mentioned here to even begin to master the art of trigger design and implementation. The Sybase documentation and a plethora of third-party manuals available at your local bookstore are valuable resources for understanding the power of triggers as part of your database design, and the finesse you need to use them. This overview should help you to begin to utilize them and understand what they can to do.

Print/View all Posts Comments on this article

sybase open server technologyNaval  | 05/25/02

White Papernyc_guru_sorta  | 05/28/02