Database trigger

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for keeping the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should be created also in the tables of the taxes, vacations, and salaries.

Contents [hide] 1 The need and the usage 2 Triggers in Oracle 2.1 Schema-level triggers 2.2 Mutating tables 3 Triggers in Microsoft SQL Server 4 Triggers in PostgreSQL 5 Triggers in Firebird 6 Triggers in MySQL 7 Triggers in IBM DB2 LUW 8 Triggers in SQLite 9 Triggers in XML databases 10 References 11 External links

[edit] The need and the usage

Triggers are commonly used to:

• prevent changes (e.g. prevent an invoice from being changed after it's been mailed out)
• log changes (e.g. keep a copy of the old data)
• audit changes (e.g. keep a log of the users and roles involved in changes)
• enhance changes (e.g. ensure that every change to a record is time-stamped by the server's clock, not the )
• enforce business rules (e.g. require that every invoice have at least one line item)
• execute business rules (e.g. notify a manager every time an employee's bank account number changes)
• replicate data (e.g. store a record of every change, to be shipped to another database later)
• enhance performance (e.g. update the account balance after every detail transaction, for faster queries)

The examples above are called Data Manipulation Language (DML) triggers because the triggers are defined as part of the Data Manipulation Language and are executed at the time the data are manipulated. Some systems also support non-data triggers, which fire in response to Data Definition Language (DDL) events such as creating tables, or runtime or and events such as logon, commit, and rollback. Such DDL triggers can be used for auditing purposes.
The following are major features of database triggers and their effects:

• triggers do not accept parameters or arguments (but may store affected-data in temporary tables)
• triggers cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)

[edit] Triggers in Oracle

In addition to triggers that fire when data is modified, Oracle 9i supports triggers that fire when schema objects (that is, tables) are modified and when user logon or logoff events occur. These trigger types are referred to as "Schema-level triggers".

[edit] Schema-level triggers

• After Creation
• Before Alter
• After Alter
• Before Drop
• After Drop
• Before Logoff
• After Logon

The four main types of triggers are:

1. Row Level Trigger: This gets executed before or after any column value of a row changes
2. Column Level Trigger: This gets executed before or after the specified column changes
3. For Each Row Type: This trigger gets executed once for each row of the result set caused by insert/update/delete
4. For Each Statement Type: This trigger gets executed only once for the entire result set, but fires each time the statement is executed.

[edit] Mutating tables

When a single SQL statement modifies several rows of a table at once, the order of the operations is not well-defined; there is no "order by" clause on "update" statements, for example. Row-level triggers are executed as each row is modified, so the order in which trigger code is run is also not well-defined. Oracle protects the programmer from this uncertainty by preventing row-level triggers from modifying other rows in the same table – this is the "mutating table" in the error message. Side-effects on other tables are allowed, however.
One solution is to have row-level triggers place information into a temporary table indicating what further changes need to be made, and then have a statement-level trigger fire just once, at the end, to perform the requested changes and clean up the temporary table.
Because a foreign key's referential actions are implemented via implied triggers, they are similarly restricted. This may become a problem when defining a self-referential foreign key, or a cyclical set of such constraints, or some other combination of triggers and CASCADE rules (e.g. user deletes a record from table A, CASCADE rule on table A deletes a record from table B, trigger on table B attempts to SELECT from table A, error occurs.)

[edit] Triggers in Microsoft SQL Server

Microsoft SQL Server supports triggers either after or instead of an insert, update, or delete operation. They can be set on tables and views with the constraint that a view can be referenced only by an INSTEAD OF trigger.
Microsoft SQL Server 2005 introduced support for Data Definition Language (DDL) triggers, which can fire in reaction to a very wide range of events, including:

• Drop table
• Create table
• Alter table
• Login events

A full list is available on MSDN.
Performing conditional actions in triggers (or testing data following modification) is done through accessing the temporary Inserted and Deleted tables.

[edit] Triggers in PostgreSQL

PostgreSQL introduced support for triggers in 1997. The following functionality in SQL:2003 is not implemented in PostgreSQL:

• SQL allows triggers to fire on updates to specific columns; As of version 9.0 of PostgreSQL this feature is also implemented in PostgreSQL.
• The standard allows the execution of a number of SQL statements other than SELECT, INSERT, UPDATE, such as CREATE TABLE as the triggered action. This can be done through creating a stored procedure to call CREATE TABLE.^[1]

Synopsis:

[edit] Triggers in Firebird

Firebird supports multiple row-level, BEFORE or AFTER, INSERT, UPDATE, DELETE (or any combination thereof) triggers per table, where they are always "in addition to" the default table changes, and the order of the triggers relative to each other can be specified where it would otherwise be ambiguous (POSITION clause.) Triggers may also exist on views, where they are always "instead of" triggers, replacing the default updatable view logic. (Before version 2.1, triggers on views deemed updatable would run in addition to the default logic.)
Firebird does not raise mutating table exceptions (like Oracle), and triggers will by default both nest and recurse as required (SQL Server allows nesting but not recursion, by default.) Firebird's triggers use NEW and OLD context variables (not Inserted and Deleted tables,) and provide UPDATING, INSERTING, and DELETING flags to indicate the current usage of the trigger.
As of version 2.1, Firebird additionally supports the following database-level triggers:

• CONNECT (exceptions raised here prevent the connection from completing)
• DISCONNECT
• TRANSACTION START
• TRANSACTION COMMIT (exceptions raised here prevent the transaction from committing, or preparing if a two-phase commit is involved)
• TRANSACTION ROLLBACK

Database-level triggers can help enforce multi-table constraints, or emulate materialized views. If an exception is raised in a TRANSACTION COMMIT trigger, the changes made by the trigger so far are rolled back and the client application is notified, but the transaction remains active as if COMMIT had never been requested; the client application can continue to make changes and re-request COMMIT.
Syntax for database triggers:

[edit] Triggers in MySQL

MySQL 5.0.2 introduced support for triggers. Some of the triggers MySQL supports are

• Insert Trigger
• Update Trigger
• Delete Trigger

The SQL:2003 standard mandates that triggers give programmers access to record variables by means of a syntax such as REFERENCING NEW AS n. For example, if a trigger is monitoring for changes to a salary column one could write a trigger like the following:

[edit] Triggers in IBM DB2 LUW

IBM DB2 for distributed systems known as DB2 for LUW (LUW means Linux Unix Windows) supports 3 trigger types: Before trigger, After trigger and Instead of trigger. Both statement level and row level triggers are supported. If there are more triggers for same operation on table then firing order is determined by trigger creation data. Since version 9.7 IBM DB2 supports autonomous transactions [1].
Before trigger is for checking data and deciding if operation should be permitted. If exception is thrown from before trigger then operation is aborted and no data are changed. In DB2 before triggers are read only — you cant modify data in before triggers. After triggers are designed for post processing after requested change was performed. After triggers can write data into tables and unlike some other databases you can write into any table including table on which trigger operates. Instead of triggers are for making views writeable.
Triggers are usually programmed in SQL PL language.

[edit] Triggers in SQLite

SQLite only supports row-level triggers, not statement-level triggers.
Updateable views, which are not supported in SQLite, can be emulated with INSTEAD OF triggers.

[edit] Triggers in XML databases

An example of implementation of triggers in non-relational database can be Sedna, that provides support for triggers based on XQuery. Triggers in Sedna were designed to be analogous to SQL:2003 triggers, but natively base on XML query and update languages (XPath, XQuery and XML update language).
A trigger in Sedna is set on any nodes of an XML document stored in database. When these nodes are updated, the trigger automatically executes XQuery queries and updates specified in its body. For example, the following trigger cancels person node deletion if there are any open auctions referenced by this person:

CREATE TRIGGER "trigger3"  
    BEFORE DELETE  
    ON doc("auction")/site//person  
    FOR EACH NODE  
    DO  
    {  
       if(exists($WHERE//open_auction/bidder/personref/@person=$OLD/@id))  
       then ( )  
       else $OLD;  
    }

...................................................................................

A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created. The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached.

SET NOCOUNT ON

CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
go
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Sou_Desc) VALUES ('Test 1')

-- Results --

Apr 28 2001  9:56AM

This example is shown for illustrative purposes only. I'll cover an example later in the article that shows a real-world world use of triggers.

When to Use Triggers

There are more than a handful of developers who are not real clear when triggers should be used. I only use them when I need to perform a certain action as a result of an INSERT, UPDATE or DELETE and ad hoc SQL (aka SQL Passthrough) is used. I implement most of my data manipulation code via stored procedures and when you do this the trigger functionality can be moved into the procedure. For example, let's say you want to send an email to the Sales Manager when an order is entered whose priority is high. When ad hoc SQL is used to insert the Orders row, a trigger is used to determine the OrderPriority and send the email when the criteria is met. The following shows a partial code listing of what this looks like.

CREATE TABLE Orders (Ord_ID int IDENTITY, Ord_Priority varchar(10))
go
CREATE TRIGGER tr_Orders_INSERT
ON Orders
FOR INSERT
AS
IF (SELECT COUNT(*) FROM inserted WHERE Ord_Priority = 'High') = 1
 BEGIN
  PRINT 'Email Code Goes Here'
 END
go
INSERT Orders (Ord_Priority) VALUES ('High')

-- Results --

Email Code Goes Here

When the stored procedure approach is used you can move the trigger code into the procedure and it looks like this.

CREATE PROCEDURE ps_Orders_INSERT
@Ord_Priority varchar(10)
AS
BEGIN TRANSACTION
 INSERT Orders (Ord_Priority) VALUES (@Ord_Priority)

 IF @@ERROR <> 0
  GOTO ErrorCode

 IF @Ord_Priority = 'High'
  PRINT 'Email Code Goes Here'

COMMIT TRANSACTION

ErrorCode:
 IF @@TRANCOUNT <> 0
  PRINT 'Error Code'
go

Let's take a look at the trigger example. The first thing you probably noticed is that the SELECT references a table called inserted. Triggers make use of two special tables called inserted and deleted. The inserted table contains the data referenced in an INSERT before it is actually committed to the database. The deleted table contains the data in the underlying table referenced in a DELETE before it is actually removed from the database. When an UPDATE is issued both tables are used. More specifically, the new data referenced in the UPDATE statement is contained in inserted and the data that is being updated is contained in deleted.
The example makes an assumption about how data is going to be added to the table. The IF statement is looking for a count of 1. This means the trigger assumes only one row will be added to the table at a time. If more than one row is added to the table in a single statement you may miss an order with a High priority because the trigger only fires once for each associated statement. I realize this may sound a little confusing so let's take a look at two more examples. The following shows that the trigger fires for each INSERT statement executed.

INSERT Orders (Ord_Priority) VALUES ('High')
INSERT Orders (Ord_Priority) VALUES ('High')

-- Results --

Email Code Goes Here

Email Code Goes Here

Now we have three rows in Orders whose Ord_Priority is High. Let's insert new rows based on the current contents of Orders to show how a trigger behaves when a multi-row statement is executed.

INSERT Orders
SELECT Ord_Priority FROM Orders

The 'Email Code Here' message is not displayed even though three new rows were added with a priority of High because the IF statement criteria was not satisfied. A trigger fires only once per statement, so the actual COUNT(*) associated with the INSERT is 3. The following shows how to modify the code to handle a multi-row INSERT.

ALTER TRIGGER tr_Orders_INSERT
ON Orders
FOR INSERT
AS
IF EXISTS (SELECT * FROM inserted WHERE Ord_Priority = 'High')
 BEGIN
  DECLARE @Count tinyint
  SET @Count = (SELECT COUNT(*) FROM inserted WHERE Ord_Priority = 'High')
  PRINT CAST(@Count as varchar(3))+' row(s) with a priority of High were entered'
 END
go

We can test the code using the same INSERT with a SELECT as follows.

INSERT Orders
SELECT Ord_Priority FROM Orders

-- Results --

12 row(s) with a priority of High were entered

A Real-World Example

Those of you familiar with web site management know that counting the traffic on a site is key in determining which areas of the site are being used. Internet Information Server (IIS) has logging capabilities that tracks a number of attributes associated with each visitor. For example, every time a visitor accesses a page on a site that page and the user's information is logged. By default the data is logged in a text file, but you can alter the default behavior and log the data to an ODBC-compliant data store.
I used this approach for a client a while back because they wanted a simple way to track the activity for each major area of their site. A major area was defined as the sections listed on the site's main navigation bar (e.g., Home, About Us, Services, ...). The goal was to produce a report that showed the number of visits to each of the main areas of the site on a per month basis. A few of you may be wondering why a trigger is needed to implement this solution. After all, a SELECT with a WHERE clause to filter the date range and GROUP BY to count the instances per page will do the trick and no triggers are needed.
The reason I decided to use a trigger-based solution had to do with the unacceptable execution time of the report. Even on a low-traffic site the number of rows in the logging table grows at a staggering rate. For every page accessed by a visitor, there is at least one row added to the table. When a page contains a reference to a graphic (e.g., .gifs or .jpgs), there is another row created. If a page contains five references to graphics, there are six rows created in the logging table every time it is accessed.
The bottom-line is that because of the size of the table the report took too long to execute. In order to reduce the time it took to execute the report I decided to use a summary (aka aggregate) table to count the page views as they were entered into the logging table. Since there were only eight main areas on the site, the summary table contained eight rows and the report ran in less than one second.
The following uses a dummied-down schema to show how this technique works. For the sake of brevity, I will only use two main areas of the site.

CREATE TABLE InetLog (ClientHost varchar(255), LogTime datetime, Target 
varchar(255))
go
CREATE TABLE LogSummary (LogSum_Category varchar(30), LogSum_Count int)
go
INSERT LogSummary VALUES ('About Us',0)
INSERT LogSummary VALUES ('Services',0)

InetLog is the main logging table and LogSummary is the summary table. The two main areas of the site are About Us and Services. The goal of the trigger is to update the value in LogSum_Count every time the AboutUs.htm and Services.htm pages are accessed. The trigger used to do this is shown here.

CREATE TRIGGER tr_InetLog_INSERT
ON InetLog
FOR INSERT
AS

IF EXISTS (SELECT * FROM inserted WHERE Target = 'AboutUs.htm')
 BEGIN
  UPDATE LogSummary
  SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'AboutUs.htm')
  WHERE LogSum_Category = 'About Us'
 END

IF EXISTS (SELECT * FROM inserted WHERE Target = 'Services.htm')
 BEGIN
  UPDATE LogSummary
  SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'Services.htm')
  WHERE LogSum_Category = 'Services'
 END
go

The trigger simply extends on the examples presented earlier and when the IF criteria is met the associated row in LogSummary is updated. The following shows the trigger works as expected.

INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:00:50','Default.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:01:01','AboutUs.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:02:01','Services.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:03:01','Products.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:04:50','Default.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:05:01','AboutUs.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:06:01','Services.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:07:01','Products.htm')
go
SELECT * FROM LogSummary

-- Results --

LogSum_Category                LogSum_Count
------------------------------ ------------
About Us                       2
Services                       2

Before I leave this section I must mention that this homemade solution is not the preferred way to monitor web site traffic. I certainly had fun researching ODBC-logging and writing the code, but I actually suggested the client buy a commercial software package like WebTrends to implement this functionality. WebTrends is a great product and allows you to perform detailed analysis of a site's traffic. The client did not want to spend any money on real software, but instead on me:))

Part II

In the next installment of this article I will discuss UPDATE, DELETE and INSTEAD OF Triggers. For those of you who have not worked with SQL Server 2000, you probably have not heard of INSTEAD OF triggers. They were added in the latest version of product to help with updatable views. Part II is posted!





.............................................................................................................................................................