Jazz Jazz - 24 days ago 9
SQL Question

Trigger: How does the inserted table work? How to access its rows?

I have the following table

Data --Table name
ID -- Identity column
PCode -- Postal Code


I created the following trigger:

CREATE TRIGGER Trig
ON Data
FOR INSERT
AS
BEGIN

Select * from inserted

END


And inserted the following values

INSERT INTO Data VALUES (125)
INSERT INTO Data VALUES (126)
INSERT INTO Data VALUES (127)


It shows this:

enter image description here

But I was expecting something like this:


  • After the 1st insertion, the trigger is executed -> one row is shown in the
    inserted
    table.

  • After the 2nd insertion, the trigger is executed -> two rows are shown in the
    inserted
    table.

  • After the 3rd insertion, the trigger is executed -> three rows are shown in the
    inserted
    table.



According to msdn.microsoft all the rows inserted are in this table.




How can I access the
inserted
table so that I can see all the expected rows and not separately?

TT. TT.
Answer

You can not. From the Use the inserted and deleted Tables article on microsoft.com, you can read:

The inserted table stores copies of the affected rows during INSERT and UPDATE statements.

That means that the inserted table will only contain rows for the current INSERT or UPDATE statement.

If you do want to see all rows for several such INSERT or UPDATE statements, you will have to store these rows in a table you created yourself.