SQL Trigger

Triggers in MSSQL are very useful but we need to take some precautions about it because of use of nested triggers the SQL Server only go up to 32 nested triggers. Triggers can be used in different situations like:

• Insert, Update, Delete  

Using the AFTER keyword that is default or using FOR keyword

Example using FOR and deleted :

CREATE TRIGGER Save_Deleted_records

ON dbo.able_Test

FOR DELETE

AS

INSERT deleted_records_mark

SELECT * FROM deleted

more on microsoft Web Site:http://msdn.microsoft.com/en-us/library/ms190739(SQL.90).aspx

Example using a AFTER Keyword

Create TRIGGER dbo.UpdateTrigger ON dbo.Table3 

AFTER INSERT AS 

 BEGIN 

   SET NOCOUNT ON; 

     declare @ticket  as varchar(8) 

     set @ticket = (select callid from inserted)

     update subset set uticketrem = @ticket where custtype<>'Facility' and callid=@ticket

END

• Instead of Insert, Update, Delete 

these means that you can do other operations like adding or deleting records


CREATE TRIGGER Example_Trigger on Table1
INSTEAD OF INSERT
AS
BEGIN
  -- Instead of inserting you can do other operations
  INSERT INTO Table45
       SELECT RowMatCol, Date_Doc
       FROM inserted
END


More on Microsoft Web Site here: http://technet.microsoft.com/en-us/library/ms175089.aspx

Happy Christmas and an Happy New Year 

Sponsored by IT Tech BuZ