Brief Introduction to Triggers in SQL Server 2000 - After Triggers

(Page 2 of 4 )

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 an 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 coloumn 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 used 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 form 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 statement issued by your users, or to 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.

Next: Multiple After and Instead Of Triggers >>
 

More MS SQL Server Articles
More By Mayank Gupta

Comments On: Brief Introduction to Triggers in SQL Server 2000

>>> Be the FIRST to comment on this article!