SQL Server Performance

update trigger

Discussion in 'Getting Started' started by sudhirbharti, Jan 29, 2009.

  1. sudhirbharti New Member

    I am writing a trigger where i need to execute the db mail on the basis of field condition.That means if a user update a field like city where city like '%p%' or city like '%s%' then it send the mail.
    program :create TRIGGER sendMailON [userinfo] FOR UPDATEAS IF UPDATE (city) BEGIN declare @UserID varchar(10) declare @body varchar(2000) declare @OldCity varchar(10) declare @NewCity varchar(10) SELECT @UserID = userid, @OldCity= d.City FROM deleted d SELECT @NewCity = City FROM inserted SET @body = 'User ID=' +@UserID+ ' has been updated with previous name is '+@OldCity+ ' and the new name is ' +@NewCityExecute msdb.dbo.sp_send_dbmail@profile_name = 'XYZManagement',@recipients = 'sbharti_22k1@rediffmail.com;sudhirbhart22i@gmail.com',@subject = 'user Information ',@body = @body,@query = NULL,@importance = 'normal'ENDGO----------------
    Note : the trigger is working fine and it sending the mail when we update the city column. but i am unable to place the like statement by which it only send the mail if updated city started with 's' or 'p'Any ideas?
  2. Adriaan New Member

    After copying the value from the record in deleted into @City, do:
    IF @City LIKE 's%' OR @City LIKE 'p%'
    BEGIN
    ........... your procedure here .......
    END
    Please note that the trigger does not cover updates of more than one row. In such a case, the deleted snapshot contains as many rows as were updated, and your trigger code will pick its values from whichever happens to be the last row in the snapshot. You can't really avoid looping through a cursor on the snapshot.
  3. sudhirbharti New Member

    yes you are right, then can you assist me to write the code for the multiple rows
  4. Adriaan New Member

    Check out cursor usage in Books Online. The basics are:
    DECLARE @var1 datatype, @var2 datatype
    DECLARE MyCursor CURSOR
    FOR
    SELECT col1, col2 FROM deleted
    OPEN MyCursor
    FETCH NEXT FROM MyCursor INTO @var1, @var2
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC well, you already have this part ...
    FETCH NEXT FROM MyCursor INTO @var1, @var2
    END
    CLOSE MyCursor
    DEALLOCATE MyCursor
    --- note that htere may be the odd syntax error here or there - look it up in Books Online!

Share This Page