CODEWITHSUNDEEP
sqlsql-servertriggers
Wahtever
Wahtever

Reputation: 3678

sql server cannot access inserted table in a trigger

I am trying to create a simple to insert trigger that gets the count from a table and adds it to another like this

CREATE TABLE [poll-count](
id VARCHAR(100),
altid BIGINT,
option_order BIGINT,
uip VARCHAR(50),
[uid] VARCHAR(100),
[order] BIGINT
PRIMARY KEY NONCLUSTERED([order]),
FOREIGN KEY ([order]) references ord ([order]  
)
GO

CREATE TRIGGER [get-poll-count]
ON [poll-count]
FOR INSERT 
AS 
    BEGIN
        DECLARE @count INT
        SET @count = (SELECT COUNT (*) FROM [poll-count] WHERE option_order = i.option_order)
        UPDATE [poll-options] SET [total] = @count WHERE [order] = i.option_order
    END
GO

when i ever i try to run this i get this error:

The multi-part identifier "i.option_order" could not be bound

what is the problem?

thanks

Upvotes: 0

Views: 1038

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

Your trigger currently assumes that there will always be one-row inserts. Have you tried your trigger with anything like this?

INSERT dbo.[poll-options](option_order --, ...)
  VALUES(1 --, ...),
        (2 --, ...);

Also, you say that SQL Server "cannot access inserted table" - yet your statement says this. Where do you reference inserted (even if this were a valid subquery structure)?

SET @count = (SELECT COUNT (*) FROM [poll-count] 
  WHERE option_order = i.option_order)
-----------------------^ "i" <> "inserted"

Here is a trigger that properly references inserted and also properly handles multi-row inserts:

CREATE TRIGGER dbo.pollupdate
ON dbo.[poll-options]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH x AS 
    (
      SELECT option_order, c = COUNT(*)
       FROM dbo.[poll-options] AS p
       WHERE EXISTS 
       (
        SELECT 1 FROM inserted 
        WHERE option_order = p.option_order
       )
       GROUP BY option_order
    )
    UPDATE p SET total = x.c
      FROM dbo.[poll-options] AS p
      INNER JOIN x 
      ON p.option_order = x.option_order;
END
GO

However, why do you want to store this data on every row? You can always derive the count at runtime, know that it is perfectly up to date, and avoid the need for a trigger altogether. If it's about the performance aspect of deriving the count at runtime, a much easier way to implement this write-ahead optimization for about the same maintenance cost during DML is to create an indexed view:

CREATE VIEW dbo.[poll-options-count]
WITH SCHEMABINDING
AS
  SELECT option_order, c = COUNT_BIG(*)
    FROM dbo.[poll-options]
    GROUP BY option_order;
GO
CREATE UNIQUE CLUSTERED INDEX oo ON dbo.[poll-options-count](option_order);
GO

Now the index is maintained for you and you can derive very quick counts for any given (or all) option_order values. You'll have test, of course, whether the improvement in query time is worth the increased maintenance (though you are already paying that price with the trigger, except that it can affect many more rows in any given insert, so...).

As a final suggestion, don't use special characters like - in object names. It just forces you to always wrap it in [square brackets] and that's no fun for anyone.

Upvotes: 3

Related Questions