using inserted and deleted tables in the triggers

Question

I want to write triggers to work with the inserted and deleted tables. I have written the trigger for inserting :

CREATE TRIGGER FILL_TABLE
ON Person FOR INSERT
AS
DECLARE @ID int
SELECT @ID = p.ID
FROM Person AS p 
    INNER JOIN inserted AS i ON p.ID = i.ID 
DECLARE @uName char(30);
SELECT @uName = SYSTEM_USER
INSERT tblOperationLog 
 Values 
 ( @uName, 'user has inserted a row with ID = ' + CONVERT(nvarchar, @ID) + '', 
    'Insert', CURRENT_TIMESTAMP, GETDATE() )

I want to write the trigger and use the deleted table just like the inserted one. but I don't know how. I want to retrieve the ID of the deleted rows to fill the second column of the tblOperationLog but I can't. Should I use the inner join in it too?

Solution 1

Your trigger will only work if only one row has been inserted. As inserted is available as a table, you can insert from that table and use all records. I think you're looking for something like this:

CREATE TRIGGER FILL_TABLE 
ON Person FOR INSERT, DELETE
AS 

  INSERT tblOperationLog  
  SELECT  SYSTEM_USER,'user has inserted a row with ID = ' + ID, 'Insert', 
    CURRENT_TIMESTAMP, getdate()
  FROM inserted

  INSERT tblOperationLog  
  SELECT  SYSTEM_USER,'user has deleted a row with ID = ' + ID, 'Delete', 
    CURRENT_TIMESTAMP, getdate()
  FROM deleted

Solution 2

In SQL server, a trigger has access to two logical tables INSERTED and DELETED that have the same structure as the table the trigger is defined.

Both tables will be filled on an UPDATE trigger. An INSERT trigger will only have the INSERTED table filled, a DELETE trigger will only have the DELETED table filled.

See MSDN.

Comments

[anna]

is there any table for updated rows too? can i use it like the above too?

[cjk]

@anna - as per @Oded's answer, when an update is performed, the rows are in the deleted table with the old information, and the inserted table with the new information