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

Question

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:

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?

Onrush

Answer 1

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.

Nice

Answer 2

There are 2 table available in a trigger, the inserted and the deleted. Each update on table XXX is actually a delete row X from XXX then an insert of row X in table XXX. So the inserted inside the trigger is a copy of what got inserted. You can do a lot with a trigger, but triggers are dangerous.

For example, on a performance gig, I found a huge SP being run by a trigger, we dropped it and the database came back online. Or another example, if you do a trigger wrong to audit logins, you can down the server.

Ischium

Answer 3

As TT mentioned, if you want to see all the inserted records then you need to change your Trigger to something like this:

CREATE TRIGGER Trig
ON Data
FOR INSERT
AS
BEGIN 

    Select * into "tablename"
    from
    (Select * from inserted) Ins

END

Medulla

Welcome to SO. When you are replying to a question, try to introduce a new point of view against whatever comment or previous reply. If you are not introducing a new POV just adding extra information, please make a comment. – Conformal