[sql] SQL Server after update trigger

I have a problem with this trigger. I would like it to update the requested information only to the row in question (the one I just updated) and not the entire table.

CREATE TRIGGER [dbo].[after_update] 
    ON [dbo].[MYTABLE]
    AFTER UPDATE
    AS 
    BEGIN
          UPDATE MYTABLE 
          SET mytable.CHANGED_ON = GETDATE(),
          CHANGED_BY=USER_NAME(USER_ID())

How do I tell the trigger that this applies only to the row in question?

you can call INSERTED, SQL Server uses these tables to capture the data of the modified row before and after the event occurs.I assume in your table the name of the key is Id

I think the following code can help you

CREATE TRIGGER [dbo].[after_update]
ON [dbo].[MYTABLE]
   AFTER UPDATE
AS
BEGIN
    UPDATE dbo.[MYTABLE]
    SET    dbo.[MYTABLE].CHANGED_ON = GETDATE(),
           dbo.[MYTABLE].CHANGED_BY = USER_NAME(USER_ID())
    FROM   INSERTED
    WHERE  INSERTED.Id = dbo.[MYTABLE].[Id]
END

Try this script to create a temporary table TESTTEST and watch the order of precedence as the triggers are called in this order: 1) INSTEAD OF, 2) FOR, 3) AFTER

All of the logic is placed in INSTEAD OF trigger and I have 2 examples of how you might code some scenarios...

Good luck...

CREATE TABLE TESTTEST
(
    ID  INT,
    Modified0 DATETIME,
    Modified1 DATETIME
)
GO
CREATE TRIGGER [dbo].[tr_TESTTEST_0] ON [dbo].TESTTEST
INSTEAD OF INSERT,UPDATE,DELETE
AS
BEGIN
    SELECT 'INSTEAD OF'
    SELECT 'TT0.0'
    SELECT * FROM TESTTEST

    SELECT *, 'I' Mode
    INTO #work
    FROM INSERTED

    UPDATE #work SET Mode='U' WHERE ID IN (SELECT ID FROM DELETED)
    INSERT INTO #work (ID, Modified0, Modified1, Mode)
    SELECT ID, Modified0, Modified1, 'D'
    FROM DELETED WHERE ID NOT IN (SELECT ID FROM INSERTED)

    --Check Security or any other logic to add and remove from #work before processing
    DELETE FROM #work WHERE ID=9 -- because you don't want anyone to edit this id?!?!
    DELETE FROM #work WHERE Mode='D' -- because you don't want anyone to delete any records

    SELECT 'EV'
    SELECT * FROM #work

    IF(EXISTS(SELECT TOP 1 * FROM #work WHERE Mode='I'))
    BEGIN
        SELECT 'I0.0'
        INSERT INTO dbo.TESTTEST (ID, Modified0, Modified1)
        SELECT ID, Modified0, Modified1
        FROM #work
        WHERE Mode='I'
        SELECT 'Cool stuff would happen here if you had FOR INSERT or AFTER INSERT triggers.'
        SELECT 'I0.1'
    END

    IF(EXISTS(SELECT TOP 1 * FROM #work WHERE Mode='D'))
    BEGIN
        SELECT 'D0.0'
        DELETE FROM TESTTEST WHERE ID IN (SELECT ID FROM #work WHERE Mode='D')
        SELECT 'Cool stuff would happen here if you had FOR DELETE or AFTER DELETE triggers.'
        SELECT 'D0.1'
    END

    IF(EXISTS(SELECT TOP 1 * FROM #work WHERE Mode='U'))
    BEGIN
        SELECT 'U0.0'
        UPDATE t SET t.Modified0=e.Modified0, t.Modified1=e.Modified1
        FROM dbo.TESTTEST t
        INNER JOIN #work e ON e.ID = t.ID
        WHERE e.Mode='U'
        SELECT 'U0.1'
    END
    DROP TABLE #work

    SELECT 'TT0.1'
    SELECT * FROM TESTTEST
END
GO
CREATE TRIGGER [dbo].[tr_TESTTEST_1] ON [dbo].TESTTEST
FOR UPDATE
AS
BEGIN
    SELECT 'FOR UPDATE'

    SELECT 'TT1.0'
    SELECT * FROM TESTTEST

    SELECT 'I1'
    SELECT * FROM INSERTED

    SELECT 'D1'
    SELECT * FROM DELETED

    SELECT 'TT1.1'
    SELECT * FROM TESTTEST
END
GO
CREATE TRIGGER [dbo].[tr_TESTTEST_2] ON [dbo].TESTTEST
AFTER UPDATE
AS
BEGIN
    SELECT 'AFTER UPDATE'

    SELECT 'TT2.0'
    SELECT * FROM TESTTEST

    SELECT 'I2'
    SELECT * FROM INSERTED

    SELECT 'D2'
    SELECT * FROM DELETED

    SELECT 'TT2.1'
    SELECT * FROM TESTTEST
END
GO

SELECT 'Start'
INSERT INTO TESTTEST (ID, Modified0) VALUES (9, GETDATE())-- not going to insert
SELECT 'RESTART'
INSERT INTO TESTTEST (ID, Modified0) VALUES (10, GETDATE())--going to insert
SELECT 'RESTART'
UPDATE TESTTEST SET Modified1=GETDATE() WHERE ID=10-- gointo to update
SELECT 'RESTART'
DELETE FROM TESTTEST WHERE ID=10-- not going to DELETE
SELECT 'FINISHED'

SELECT * FROM TESTTEST
DROP TABLE TESTTEST

You should be able to access the INSERTED table and retrieve ID or table's primary key. Something similar to this example ...

CREATE TRIGGER [dbo].[after_update] ON [dbo].[MYTABLE]
AFTER UPDATE AS 
BEGIN
    DECLARE @id AS INT
    SELECT @id = [IdColumnName]
    FROM INSERTED

    UPDATE MYTABLE 
    SET mytable.CHANGED_ON = GETDATE(),
    CHANGED_BY=USER_NAME(USER_ID())
    WHERE [IdColumnName] = @id

Here's a link on MSDN on the INSERTED and DELETED tables available when using triggers: http://msdn.microsoft.com/en-au/library/ms191300.aspx

It is very simple to do that, First create a copy of your table that your want keep the log for For example you have Table dbo.SalesOrder with columns SalesOrderId, FirstName,LastName, LastModified

Your Version archieve table should be dbo.SalesOrderVersionArchieve with columns SalesOrderVersionArhieveId, SalesOrderId, FirstName,LastName, LastModified

Here is the how you will set up a trigger on SalesOrder table

USE [YOURDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Karan Dhanu
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER dbo.[CreateVersionArchiveRow]
   ON  dbo.[SalesOrder]
  AFTER Update
AS 
BEGIN

    SET NOCOUNT ON;
INSERT INTO dbo.SalesOrderVersionArchive

   SELECT *
   FROM deleted;

END

Now if you make any changes in saleOrder table it will show you the change in VersionArchieve table

try this solution.

       DECLARE @Id INT
       DECLARE @field VARCHAR(50)

       SELECT @Id= INSERTED.CustomerId       
       FROM INSERTED

       IF UPDATE(Name)
       BEGIN
              SET @field = 'Updated Name'
       END

       IF UPDATE(Country)
       BEGIN
              SET @field = 'Updated Country'
       END

       INSERT INTO CustomerLogs
       VALUES(@Id, @field)

       // OR
       -- If you wish to update existing table records.
       UPDATE YOUR_TABLE SET [FIELD]=[VALUE] WHERE {CONDITION}

I didn't checked this with older version of sql server but this will work with sql server 2012.

Here is my example after a test

CREATE TRIGGER [dbo].UpdateTasadoresName 
ON [dbo].Tasadores  
FOR  UPDATE
AS 
      UPDATE Tasadores 
      SET NombreCompleto = RTRIM( Tasadores.Nombre + ' ' + isnull(Tasadores.ApellidoPaterno,'') + ' ' + isnull(Tasadores.ApellidoMaterno,'')    )  
      FROM Tasadores 
    INNER JOIN INSERTED i ON Tasadores.id = i.id

The inserted special table will have the information from the updated record.

CREATE TRIGGER [dbo].[after_update] ON [dbo].[MYTABLE]
AFTER UPDATE
AS
BEGIN
    DECLARE @ID INT

    SELECT @ID = D.ID
    FROM inserted D

    UPDATE MYTABLE
    SET mytable.CHANGED_ON = GETDATE()
        ,CHANGED_BY = USER_NAME(USER_ID())
    WHERE ID = @ID
END

Try this (update, not after update)

CREATE TRIGGER [dbo].[xxx_update] ON [dbo].[MYTABLE]
    FOR UPDATE
    AS
    BEGIN

        UPDATE MYTABLE
        SET mytable.CHANGED_ON = GETDATE()
            ,CHANGED_BY = USER_NAME(USER_ID())
        FROM inserted
        WHERE MYTABLE.ID = inserted.ID

    END

First off, your trigger as you already see is going to update every record in the table. There is no filtering done to accomplish jus the rows changed.

Secondly, you're assuming that only one row changes in the batch which is incorrect as multiple rows could change.

The way to do this properly is to use the virtual inserted and deleted tables: http://msdn.microsoft.com/en-us/library/ms191300.aspx