Triggers

General Info About Triggers

What are they?

Sort of like automatic stored procedures that get called whenever data in a table is inserted, updated, or deleted.

What makes triggers valuable?

A trigger can check the attempted operation and undo it (via rollback transaction) if there is referential data inconsistency or bad data.

The trigger is transparent to the client invoking the insert, update, or delete operation.

The trigger is always invoked. You don't need data consistency checking code in each client. Note that to special commands, "truncate table" and "bulkcopy", bypass triggers, but that's not a major issue.

Triggers can also be used to keep duplicate or pre-calculated data automatically up to date.

For a given table there can be separate insert, update, and delete triggers.

Inserted & Deleted Virtual Tables

The changed data (inserted/updated/deleted) is available within the trigger as one of two virtual database tables:

inserted
deleted

For inserts:: The "inserted" virtual table contains a copy of the rows that have been inserted. There can be multiple rows in this virtual table.
For updates:
The "deleted" virtual table contains a copy of the rows that are overwritten by the update. The "inserted" virtual table contains a copy of the updated rows. Once again, multiple rows can be present in the "inserted" and "deleted" tables. It is possible to test if a specific column has been updated.
For Deletes:: The "deleted" virtual table contains the rows that have been deleted.

Misc Notes on Triggers

One trigger can cause another trigger to be fired. This is called cascading triggers. Cascading triggers can be turned off on a server wide basis by the server administrator.

When the table the trigger is based on is deleted, the trigger is also deleted.

There can only be one trigger of a given type (insert/update/delete) on a given table.

A non-assignment "select" statement in a trigger will send rows back to the client. This is generally not a good idea. Typically, triggers return error messages or nothing to the client.

A trigger cannot reinvoke itself via an insert/update/delete.

You can only create triggers on tables you own. Eg. you can't create triggers in the "pubs" demo database.

Triggers and Transactions

An Example Scenario Using an Insert

Seen by the User Transparent to the User begin Transaction (possibly implicit) insert operation data is updated in target table virtual "inserted" table is created the insert trigger is called with the virtual "inserted" table as a global parameter. trigger can rollback the transaction or return normally next statement in batch runs (although not if a rollback occurred in the trigger). .... commit transaction

Notes on Transactions in Sybase

Transactions in Sybase are "unchained".

By default, individual statements are treated as if there is a begin transaction and a commit transaction.
To include multiple SQL statements into a single transaction, it is necessary to include explicit "begin transaction" and "commit transaction" statements.

ANSI Standard Transactions are Chained

ANSI standard transactions have an implicit "begin transaction" when the first select, insert, update, or delete is issued.
This is the way DB2 does it.

Sybase does not currently support chained transactions. I will be supported in Release 10 of the Sybase server.

Trigger Examples

Insert Trigger

create trigger my_insert_trigger on mytable for insert as begin declare @inserted_row_count int declare @matching_id_row_count int select @inserted_row_count=count(*) from inserted select @matching_id_row_count=count(*) from inserted a, my_other_table b where a.id = b.id if @matching_id_row_count != @inserted_row_count begin raiserror 20000 "my_insert_trigger - mytable.id not in my_other_table" rollback transaction end else begin /* ... do other processing ... */ end end /* my_insert_trigger */

Triggers and Referential Integrity

A Brief Review of Referential Integrity

Each table has a primary key. Roughly speaking, the primary key is column or set of columns which have a unique value for each row in the table.

If a table has a field which is used as the primary key in some other table, then that field is referred to as a secondary key in this table.

Data in one table which refers to another table does so via a foreign key in the local table referring to a primary key in the referred to table.

Rows of data that refer to each other must be kept consistent. For example it probably would be wrong to delete a row from a "parts" table if there were still rows in an "orders" table referring to that part. Likewise it wouldn't make sense to place an order for a part which isn't in the parts table.

Triggers on Tables with Primary/Foreign Keys

Deleting a row:

Other tables must be checked to see if they have rows with the foreign key value which is the deleted primary key value of this table.

If so, then one of the following actions should occur:

The delete can be rejected (via rollback).
The tables containing the foreign keys must have those rows deleted. This can cause cascading triggers (which is ok).
The tables containing the foreign keys must have the have the foreign key fields in those rows set to null. Application design determines whether this makes sense or is appropriate.

Inserting a row:

If the table being inserted to has foreign keys, then the table containing the corresponding primary key must be checked to see if a row with the proper primary key value exists.

If not, then the insert should be rejected (via rollback).

Referential Integrity in Release 10

The newest release of Sybase is called System 10. It was originally called release 5 (since release 4 was the last release), but marketing weenies have decided otherwise. I'm sure it's just a coincidence that Oracle just shipped release 7. Anyway....

With Release 10 of Sybase, ANSI standard declarative referential integrity is supported.

This means that the relations between primary and foreign keys are defined at the time the tables are created.

For example:

create table order_table ( order_id int not null supplier_id int null, part_id int null, qty int null, constraint order_tbl_pk primary key ( order_id ), constraint supplier_fk foreign key ( supplier_id ) references supplier_table, constraint part_fk foreign key ( part_id ) references parts_table )

Sybase Release 10 supports custom error messages that can be assigned to a specific constraint.

I think there's a 16 table limit on the number of tables involved with Sybase declarative referential constraints.

Triggers to Enforce Other Rules

Some rules go beyond simple referential integrity. Often, for performance, purposes it necessary to keep duplicate or pre- calculated data redundantly with the original data.

As an example, suppose month to date totals need to be kept online. Nightly batch jobs could be used to generate the totals. As an alternative, a set of insert, update, and delete triggers can be created used to automatically update the month to date totals as changes are made.

As another example, suppose you want to create a duplicate readonly table for performance reasons. The main table can have triggers created so that changes are automatically propagated to the duplicate table.

Next chapter.