Why we Use Triggers

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.


Some Examples:


INSERT INTO Customers

VALUES (Sheeban,Ahmed,Memon,Karachi,
Sindh,110016,01126853138)

INSERT INTO Customers

VALUES(Faizan,Ahmed,Khanani,

Chicago,illions,326541,9412658745)


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


CREATE TRIGGER SindhDel ON [Customers]

FOR DELETE

AS

IF (SELECT state FROM deleted) = Sindh

BEGIN

PRINT Can not remove customers from Sindh

PRINT Transaction has been canceled

ROLLBACK

END


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