SQL Server after update trigger

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   lt Create Date   gt  -- Description   lt Description   gt  --                                               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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Server after update trigger

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-express

Examples related to sql-server-2014