MDBitz Development Resources

A Trigger is a Stored Procedure executed upon the modification to a database table. Triggers can be set to occur on INSERT, DELETE, and UPDATE of a table row, and can even be set to happen before or after the change occurs. Upon a change to a table that has a trigger the body of the Trigger is fired performing its functionality. This allows the database manager the ability to add in checks on data validity as well as modifying additional tables if needed.

Often Triggers are used for many functions the most common being:

• Relationship Enforcement
  By use of triggers you can create complex rules for data integrity. If you find that checks and reference constraints are not enough a trigger gives you the ability to define your complex rule.
• Cascading Operations
  By use of triggers you can cascade impacts from a change to a table throughout the rest of the database. For example if a salesperson is deleted or set to inactive you may want to change the sales rep who is in charge of their accounts.
• Audit Changes
  By use of triggers you can limit changes that occur to the database by disallowing updates and changes that should not be permitted.

Although triggers can be helpful in enforcing the integrity of your database there are 2 main disadvantages to using triggers.

• Activity Masking
  By containing business logic in the database by use of triggers then activity can be masked by the end user. Meaning that a user could mean to make a simple change to a table and instead causes changes throughout the database as they were unaware of the triggers.
• Potential Performance Impact
  Triggers if mismanaged can have high performance impacts on your database. As triggers are executed on every row modification they increase activity and if a Trigger modifies a table that has another trigger they can cause large cascades of activity.

What is Pagination

Pagination is the process of displaying a subset of the data on a web page at one time. The simplest example is a google search result in that after a search is performed you are displayed with the first x results as page 1. Then utilizing the page handlers you are able to view page 2, 3, etc until there are no additional results.

Pagination and Server Side Code

When developing an application the back end is responsible for obtaining and storing the requested data to be presented to the user. There are two obtions available to developers when dealing with pagination. The first is to obtain all the data and save it in the session for which only subsets of it will be displayed depending on the page the user is viewing. While the second method is to obtain only the data that is for the current page and when a new page is requested then that data is obtained.

This article deals with the second method as the smaller data set will allow for the results to be returned to the user faster as well as having a smaller strain on the resources of the server.

Limitations of Sybase

When utilizing a Sybase database as the data warehouse for your application there are difficulties in creating this dynamic pagination as there is no LIMIT or OFFSET commands in the Transact-SQL (T-SQL) language.  In other SQL languages these commands allow you to specify how many results you want (LIMIT) and where to start returning your results from (OFFSET). To overcome this limitation we will have to make use of a Stored Procedure to manipulate data into and out of a temporary database.

Implementation of Pagination

For the purposes of this tutorial lets assume we are working with a User table created by the following statement:

CREATE TABLE T_USER (
  ID INT IDENTITY,
  USER_NAME VARCHAR(50),
  LAST_NAME VARCHAR(50),
  FIRST_NAME VARCHAR(50),
  UNIQUE CLUSTERED (ID)
)

From this table we are going to want to obtain x users starting with the y^th one. So to begin lets create a stored procedure that takes 2 parameters pageSize and offset.

CREATE PROCEDURE USER_PAGINATION
@offset INT,
@pageSize INT,
AS
BEGIN
 //--content
END

Now to perform the pagination we are going to have to perform 2 queries. The first query is going to select every user in the Users table ordered by their USER_NAME and insert them into a temp table with a new identity column that equals their order in the temp table.

SELECT ID_KEY = IDENTITY(5)
  USER_NAME,
  FIRST_NAME,
  LAST_NAME
INTO #TEMPUTBL
FROM T_USER
ORDER BY USER_NAME

The second query will then select the rows from the temp table beginning with the offset upto the offset + pageSize

SELECT *
  FROM #TEMPUTBL
  WHERE
    ID_KEY > @offset and
    ID_KEY <= (@offset + @pageSize)

Putting the whole thing together we end up with:

CREATE PROCEDURE USER_PAGINATION
@offset INT,
@pageSize INT,
AS
BEGIN
  SELECT ID_KEY = IDENTITY(5)
    USER_NAME,
    FIRST_NAME,
    LAST_NAME
  INTO #TEMPUTBL
  FROM T_USER
  ORDER BY USER_NAME
 
  SELECT *
    FROM #TEMPUTBL
    WHERE
      ID_KEY > @offset and
      ID_KEY <= (@offset + @pageSize)
END

Last step is simply to run the stored procedure passing the pageSize and the offset.

execute USER_PAGINATION 50, 25

Resources

• Transact-SQL User’s Guide