how to create before update trigger in sql server 2005

Question

Is there anyway where I can create a trigger which will execute before the update/delete takes place( and then the actual update/delete takes place)? and how can I drop a trigger from a table?

Answer 1

to drop trigger- use database_name

IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'tgr_name' AND type = 'TR')    
DROP TRIGGER tgr_name
GO

Answer 2

This article from microsoft explains the syntax of creating triggers.

http://msdn.microsoft.com/en-us/library/ms189799.aspx

There isn't really a 'before' trigger, but you can use an INSTEAD OF trigger that allows you to jump in place of whatever action is attempted, then define your own action.

I've used that technique for versioning data.

CREATE TRIGGER [dbo].[Documents_CreateVersion] 
   ON  [dbo].[Documents] 
   INSTEAD OF UPDATE

AS 
BEGIN

  DECLARE @DocumentID int
  SELECT DocumentID  = DocumentID FROM INSERTED

  -- do something

END

INSERTED is a bit of a misnomer here, but it contains the details of the action before it occurs you can then define your own action with that data.

Edit:

As per comments below my response, my example can be dangerous if multiple rows are updated at once. My application doesn't allow for this so it's fine in this case. I would agree that the above is a bad practice regardless.

Answer 3

to drop a trigger use:

--SQL Server 2005+, drop the trigger, no error message if it does not exist yet
BEGIN TRY DROP TRIGGER dbo.TrigerYourTable END TRY BEGIN CATCH END CATCH
GO

--drop trigger pre-SQl Server 2005, no error message if it does not exist yet
if exists (select * from sysobjects where id = object_id(N'[dbo].[TrigerYourTable ]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
    drop trigger [dbo].[TrigerYourTable ]
GO

OP said in a comment:

...suppose I have to check childcount of a perticular user if that is more than 5 do not update the user.how can I do that using instead of trigger?

You don't really need to prevent the original update, you can let it happen, and then in the trigger check for the problem and rollback if necessary. This is how to enforce the logic for one or many affected rows, when you need to JOIN to determine the childcount of the affected rows:

--create the trigger
CREATE TRIGGER dbo.TrigerYourTable ON dbo.YourTable
   AFTER UPDATE
AS 
SET NOCOUNT ON

IF EXISTS (SELECT
               1
               FROM INSERTED                      i
                   INNER JOIN YourChildrenTable   c ON i.ParentID=c.ParentID
               GROUP BY i.ParentID
               HAVING COUNT(i.ParentID)>5
          )
BEGIN
    RAISERROR('Count of children can not exceed 5',16,1)
    ROLLBACK
    RETURN
END

GO

It will throw the error if there is a violation of the logic, and the original command will be subject to a rollback.

If childcount is a column within the affected table, then use a trigger like this to enforce the logic:

--create the trigger
CREATE TRIGGER dbo.TrigerYourTable ON dbo.YourTable
   AFTER UPDATE
AS 
SET NOCOUNT ON

IF EXISTS (SELECT 1 FROM INSERTED WHERE childcount>5)
BEGIN
    RAISERROR('Count of children can not exceed 5',16,1)
    ROLLBACK
    RETURN
END

GO

If you just want to ignore the update for any rows that violate the rule try this:

--create the trigger
CREATE TRIGGER dbo.TrigerYourTable ON dbo.YourTable
   INSTEAD OF UPDATE
AS 
SET NOCOUNT ON

UPDATE y
    SET col1=i.col1
       ,col2=i.col2
       ,col3=i.col3
       ,....  --list all columns except the PK column!
    FROM dbo.YourTable       y
        INNER JOIN INSERTED  i on y.PK=i.PK
    WHERE i.childcount<=5
GO

It will only update rows that have a child count less than 5, ignoring all affected rows that fail the requirement (no error message).