Click here to Skip to main content
Click here to Skip to main content

Brief about Triggers in SQL Server 2000

, 17 Jun 2004
Rate this:
Please Sign up or sign in to vote.
A trigger is a database object that is bound 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".

Introduction

A trigger is a database object that is bound 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".

When to Use Triggers

There are many reasons to use triggers. If you have a table which keeps a log of messages, you may want to have a copy of them mailed to you if they are urgent. If there were no triggers, you would have some solutions, though they are not as elegant. You could modify the application(s) logging the messages. This means that you might be redundantly coding the same thing in every application that logs messages.

Tables can have multiple triggers. The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column.

SQL Server 2000 greatly enhances trigger functionality, extending the capabilities of the triggers you already know and love, and adding a whole new type of trigger, the "Instead Of" trigger.

SQL Server 2000 has many types of triggers:

  1. After Trigger
  2. Multiple After Triggers
  3. Instead Of Triggers
  4. Mixing Triggers Type

After Triggers

Triggers that run after an update, insert, or delete can be used in several ways:

  • Triggers can update, insert, or delete data in the same or other tables. This is useful to maintain relationships between data or to keep audit trail information.
  • Triggers can check data against values of data in the rest of the table or in other tables. This is useful when you cannot use RI constraints or check constraints because of references to data from other rows from this or other tables.
  • Triggers can use user-defined functions to activate non-database operations. This is useful, for example, for issuing alerts or updating information outside the database.

Note: An AFTER trigger can be created only on tables, not on views.

How to Create After Triggers

  1. Working with INSERT Triggers
    INSERT INTO Customers 
    VALUES (‘Mayank’,’Gupta’,’Hauz Khas’,’Delhi’,
                  ’Delhi’,’110016’,’01126853138’)
    INSERT INTO Customers 
    VALUES(‘Himanshu’,’Khatri’,’ShahjahanMahal ’,
      ’Jaipur’,’Rajesthan’,’326541’,’9412658745’)
    INSERT INTO Customers 
    VALUES (‘Sarfaraz’,’Khan’,’Green Market’,
       ’Hydrabad’,’AP’,’698542’,’9865478521’)
     
    INSERT INTO Products
    VALUES (‘ASP.Net Microsoft Press’,550)
    INSERT INTO Products
    VALUES (‘ASP.Net Wrox Publication’,435)
    INSERT INTO Products
    VALUES (‘ASP.Net Unleased’,320)
    INSERT INTO Products
    VALUES (‘ASP.Net aPress’,450)
     
    CREATE TRIGGER invUpdate ON [Orders]
    FOR INSERT
    AS
    UPDATE p SET p.instock=[p.instock – i.qty]
    FROM products p JOIN inserted I ON p.prodid = i.prodid

    You created INSERT trigger that referenced the logical inserted table. Whenever you insert a new record in the Orders table now, the corresponding record in the Products table will be updated to subtract the quantity of the order from the quantity on hand in the instack column of the Products table.

  2. Working with DELETE Triggers

    DELETE triggers are used for restricting the data that your users can remove from a database. For example:

    CREATE TRIGGER DelhiDel ON [Customers]
    FOR DELETE
    AS
    IF (SELECT state FROM deleted) = ‘Delhi’
    BEGIN
    PRINT ‘Can not remove customers from Delhi’
    PRINT ‘Transaction has been canceled’
    ROOLBACK
    END

    DELETE trigger uses the logical deleted table to make certain that you were not trying to delete a customer from the great state “Delhi” – if you did try to delete such a customer, you would be met with Mayank in the from of an error message (which was generated by the PRINT statement that you entered in the trigger code).

  3. Working with UPDATE Triggers

    UPDATE triggers are used to restrict UPDATE statements issued by your users, or back your previous data.

    CREATE TRIGGER CheckStock ON [Products]
    FOR UPDATE
    AS
    IF (SELECT InStock FROM inserted) < 0
    BEGIN
    PRINT ‘Cannot oversell Products’
    PRINT ‘Transaction has been cancelled’
    ROLLBACK
    END

    You created an UPDATE trigger that references the inserted table to verify that you are not trying to insert a value that is less than zero. You need to check only the inserted table because SQL Server performs any necessary mathematical functions before inserting your data.

Multiple After Triggers

More than one trigger can now be defined on a table for each Insert/Update/Delete. Although in general, you might not want to do this (it's easy to get confused if you over-use triggers), there are situations where this is ideal. One example that springs to mind is that you can split your triggers up into two categories:

  • Application based triggers (cascading deletes or validation, for example).
  • Auditing triggers (for recording details of changes to critical data).

This would allow you to alter triggers of one type without fear of accidentally breaking the other.

If you are using multiple triggers, it is of course essential to know which order they fire in. A new stored procedure called sp_settriggerorder allows you to set a trigger to be either the "first" or "last" to fire.

If you want more than two triggers to fire in a specific order, there is no way to specifically define this. A deeply unscientific test I did indicated that multiple triggers for the same table and operation will run in the order they were created unless you specifically tell them otherwise. I would not recommend relying on this though.

Instead Of Triggers

Instead Of Triggers fire instead of the operation that fires the trigger, so if you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, they will not actually get deleted (unless you issue another delete instruction from within the trigger) as in this simple example:

CREATE TABLE Mayank (Name  varchar(32))
GO
 
CREATE TRIGGER tr_mayank ON Mayank 
INSTEAD OF DELETE
AS
    PRINT 'Sorry - you cannot delete this data'
GO
 
INSERT Mayank
    SELECT 'Cannot' union
    SELECT 'Delete' union
    SELECT 'Me'
GO
 
DELETE Mayank
GO
 
SELECT * FROM Mayank
GO
DROP TABLE Mayank

If you were to print out the contents of the inserted and deleted tables from inside an Instead Of trigger, you would see they behave in exactly the same way as normal. In this case, the deleted table holds the rows you were trying to delete, even though they will not get deleted.

Instead of Triggers can be used in some very powerful ways!

  • You can define an Instead Of trigger on a view (something that will not work with After triggers) and this is the basis of the Distributed Partitioned Views that are used so split data across a cluster of SQL Servers.
  • You can use Instead Of triggers to simplify the process of updating multiple tables for application developers.
  • Mixing Trigger Types.

If you were to define an Instead Of trigger and an After trigger on the same table for the same operation, what would happen?

Because an After trigger fires after an operation completes, and an 'instead of' trigger prevents the operation from taking place, the After trigger would never fire in this situation.

However, if an Instead Of trigger on a (say) delete operation contains a subsequent delete on the same table, then any After trigger defined for the delete operation on that table will fire on the basis of the delete statement issued from the Instead Of trigger. The original delete statement is not executed, only the Delete in the Instead Of trigger runs.

This code sample creates a trigger of each type, and changed the nature of the delete statement issued so that only comics that have a value of 0 in the Preserve column can be deleted.

CREATE TABLE Gupta (Comic VARCHAR (32), Preserve INT)
GO
 
INSERT Gupta
    SELECT 'groucho', 1 UNION
    SELECT 'chico', 1 UNION
    SELECT 'harpo', 0 UNION
    SELECT 'zeppo', 0
GO
 
CREATE TRIGGER trGuptaDelete ON Gupta 
FOR DELETE
AS
    SELECT Comic AS "deleting_these_names_only"
    FROM deleted
GO
 
CREATE TRIGGER tr_Gupta_InsteadOf ON Gupta
INSTEAD OF DELETE
AS
    DELETE Gupta
    FROM Gupta
    INNER JOIN Deleted
    ON Gupta.Comic = Deleted.Comic
    WHERE Gupta.Preserve= 0
GO
 
DELETE Gupta WHERE Comic IN ('GROUCHO', 'HARPO')
GO
 
SELECT * FROM Gupta
 
DROP TABLE Gupta

Important

Triggers can be used in the following scenarios, such as: if the database is de-normalized and requires an automated way to update redundant data contained in multiple tables, or if customized messages and complex error handling are required, or if a value in one table must be validated against a non-identical value in another table.

Triggers are a powerful tool that can be used to enforce the business rules automatically when the data is modified. Triggers can also be used to maintain the data integrity. But they are not to maintain data integrity. Triggers should be used to maintain the data integrity only if you are unable to enforce the data integrity using CONSTRAINTS, RULES, and DEFAULTS. Triggers cannot be created on the temporary tables.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

SquaredRomi
Web Developer
United States United States
MCSD.NET + MCAD.NET + MCP + OCA

Comments and Discussions

 
QuestionSrigger for chart update PinmemberAmi_Modi11-Feb-14 0:42 
GeneralMy vote of 5 Pinmember9 from ap9-Mar-12 22:37 
GeneralMy vote of 4 Pinmemberonkarnath200131-Aug-11 1:49 
GeneralGood Job PinmemberRatnesh N Bharos17-May-11 2:17 
GeneralGood Article PinmemberPrasanta_Prince20-Apr-11 2:52 
GeneralTriggers in sql server Pinmemberamitrajahuja12-Jan-11 21:08 
GeneralMy vote of 2 PinmemberMember 353020331-May-10 3:29 
GeneralMy vote of 2 Pinmembervinmca8-Dec-09 23:31 
Generalplease help Pinmemberhazem alkhaldi30-Jun-09 4:34 
Generalnice article Pinmemberdk gohel18-May-09 1:49 
GeneralGood article PinmemberDonsw15-Jan-09 7:53 
Generalthank you PinmemberAbu jamil12-Nov-07 2:25 
QuestionHow many times trigger is fired? Pinmemberforprashantpatil29-Jul-07 20:55 
GeneralRe: How many times trigger is fired? Pinsussniraj9-Dec-07 20:00 
GeneralVery helpful article PinmemberDivesh4u3-Jun-07 22:00 
GeneralGood Pinmemberykg9-May-06 1:33 
GeneralComment on Article PinmemberKarthikeyan Muthurajan6-Sep-04 18:36 
GeneralOops Pinmemberc-sharp5-Jun-04 4:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 18 Jun 2004
Article Copyright 2004 by SquaredRomi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid