Triggers in sql server 2005

Some useful query related to Trigger in SQL SERVER 2005


***** how to create triggers in sql server 2005 ? *****


Create trigger your_trigger_name ON your_table_name
FOR Your_Operation -- For example : For Update or For Insert, For Delete, After Update, After Insert etc
AS
BEGIN
-- Your Input Output Parameter Declaration
SELECT @PARA1 = (COLUMN_NAME) FROM INSERTED
SELECT @PARA2 = (COLUMN_NAME) FROM DELETED
-- Your DATA Manipulation Operation Here
INSERT, UPDATE , DELETE
END
GO

Example Here:
 
FOR UPDATE TRIGGER IN SQL SERVER 2005


CREATE TRIGGER AfterUpdateTriggerTest ON TriggerTblCustomer
FOR UPDATE
AS
DECLARE @field AS INT
DECLARE @field1 AS VARCHAR(100)
BEGIN
    SELECT @field = (PKCUSTID) FROM INSERTED
    SELECT @field1 = (FNAME) FROM INSERTED
    UPDATE S SET name = @field1
    FROM TriggerTblSalesman AS S
    JOIN TriggerTblCustomer AS C
    ON C.PKCUSTID = S.FKCUSTID WHERE C.PKCUSTID = @field
END
GO


***** Search trigger text in SQL Server 2005 *****


SELECT text FROM syscomments WHERE [text] LIKE '%trigger%' AND
-- if you want a trigger attached to specific table then
[text] LIKE '%On your_trigger_name%'


SP_HELPTEXT your_trigger_name


***** Find all triggers in database *****

SELECT * FROM sys.triggers

***** Delete trigger from database *****

DROP TRIGGER your_trigger_name