Update trigger insert Null

Question

Hi! I am trying to create trigger on SQL Server 2008. I want that if i update field in tabele log that the new value update field in another table Doc. This is the code for trigger:

Create TRIGGER dbo.DocSt
   ON  dbo.log 
   AFTER  UPDATE
IF (SELECT COUNT(*) FROM inserted) > 0 
    BEGIN 
        IF (SELECT COUNT(*) FROM deleted) > 0 
        BEGIN 
          UPDATE [dbo].[Doc]
   SET 
      [ID_user] = (select ID_user from inserted)
 WHERE 
 IDN=  (select id_doc from inserted)
        END 
       END 

When I update field in table log triger update table Doc but it insert NULL. What i am doing wrong? Thanks!

Answer 1

This code won't ever work - what happens in your UPDATE statement updates 10 rows?? What does this select give you:

SET [ID_user] = (select ID_user from inserted)

You're trying to set a single value to a whole return set from a SELECT statement - that won't work, obviously.

You need to create an UPDATE statement that joins with the Inserted pseudo-table:

CREATE TRIGGER dbo.DocSt
   ON dbo.log AFTER UPDATE

   UPDATE [dbo].[Doc]
   FROM Inserted i
   SET [ID_user] = i.ID_User
   WHERE IDN = i.id_doc

That way, for each entry in Inserted, you're joining your table dbo.Doc to it and update the ID_user column.