Get Ping with every DML statement on your table.....TRIGGERS

A trigger is a database object that is Bind to a table. In many aspects it is similar to a stored procedure and are often referred to as a "special kind of stored procedure."  

Modifications to the table are made using INSERT,UPDATE,OR DELETE statements.Triggers are used to enforce data integrity and business rules such as automatically updating summary data. It allows to perform cascading delete or update operations. If constraints exist on the trigger table,they are checked prior to the trigger execution. If constraints are violated statement will not be executed and trigger will not run.Triggers are associated with tables and they are automatic . Triggers are automatically invoked by SQL SERVER. Triggers prevent incorrect , unauthorized,or inconsistent changes to data.
 
 SQL Server  has many types of triggers:

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

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.

Exercising with After Triggers

1)Working with INSERT Triggers  

CREATE TRIGGER invoiceUpdate ON [Orders]
FOR INSERT
AS
Begin
UPDATE p SET p.instock=[p.instock – i.qty]
FROM products p JOIN inserted I ON p.prodid = i.prodid
End
 
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 VikramDelete ON [Customers]
FOR DELETE
AS
IF (SELECT name FROM deleted) = ‘Vikram’
BEGIN
PRINT ‘Danger...Can not remove customers with the name vikram’
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 with a great name “Vikram” – if you did try to delete such a customer, you would be met with Anaconda 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.
 
Since I am running out of time, in next post i will try to explain INSTEAD OF TRIGGER.


Post Reference: Vikram Aristocratic Elfin Share 
BlueTangledKindle Wireless Reading Device, Wi-Fi, Graphite, 6" Display with New E Ink Pearl Technology