SQL Server – What Does SET @Variable1 = (Select field1 From INSERTED) Mean?

sql-server-2008-r2trigger

I'm trying to understand an existing Trigger on one of my tables (DocumentDistribution), that it seems is the bridge table between DocumentInfo and DocumentSource tables. I don’t understand how this line (SET….FROM INSERTED) gets the value from? Is this what it called Dynamic SQL and it gets the value from the front end App for this database based on the user’s selections?

I’m getting errors when I try to test, as the sub-query is returning multiple rows while the syntax is expecting a single row, any advice?
I'm using SQL Server 2008R2.

Alter TRIGGER [dbo].[DocDist_Dup_Check] 
ON [dbo].[DocumentDistribution]
AFTER UPDATE, INSERT 

AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
DECLARE @docid int,
        @sourceid int,
        @errstr varchar(255);

SET @errstr = 'The distribution you have attempted to create already exists in the database.' + CHAR(13) + 
              'Duplicate distributions are not allowed for any document source except eBinder.';
SET @docid = (SELECT DocumentDistDocID FROM INSERTED);
SET @sourceid = (SELECT DocumentDistSourceID FROM INSERTED);

IF  (SELECT COUNT(*) FROM DocumentDistribution WHERE DocumentDistDocID = @docid AND DocumentDistSourceID = @sourceid) > 1

    AND
    (@sourceid NOT IN(SELECT DocumentSourceID FROM DocumentSource WHERE DocumentSourceName = 'eBinder' OR DocumentSourceName = 'eBinder - West Division' OR DocumentSourceName = 'Consolidated e-Binder' OR DocumentSourceName = 'MA Consolidated eBinder'))
BEGIN
    ROLLBACK TRANSACTION
END

ELSE
BEGIN
    RETURN
END

END

Best Answer

The problem is that the trigger was initially written with very simplistic logic and with minimal testing - it assumed that the update or insert could only ever affect a single row at a time.

The line you are asking about is attempting to assign a single value to a variable from a table that may contain more than one row (triggers in SQL Server fire per action, not per row). Different forms of variable assignment behave differently when there is more than one row to choose from, for example:

DECLARE @foo TABLE(id INT);
INSERT @foo VALUES(1),(2);

DECLARE @id INT;
-- this will succeed, picking a single, may-as-well-be arbitrary row:
SELECT @id = id FROM @foo;
PRINT @id;

-- this will fail:
--SET @id = (SELECT id FROM @foo); 
--PRINT @id;

If you run the second version, you will get an error message you've probably already seen from your trigger:

Msg 512, Level 16, State 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Not all is lost - your trigger can be re-written to handle multiple rows being affected - I am going to make the assumption that if you insert two rows and only one of them fails, you want the whole transaction rolled back, even though the other row was perfectly fine. (Also, your initial code populates @errstr but never uses it, so I've just left that out for now.)

ALTER TRIGGER [dbo].[DocDist_Dup_Check] 
ON [dbo].[DocumentDistribution]
AFTER UPDATE, INSERT 
AS
BEGIN
  SET NOCOUNT ON;
  SET ANSI_WARNINGS OFF;

  IF EXISTS
  (
    SELECT DocumentDistDocID, DocumentDistSourceID
    FROM dbo.DocumentDistribution AS dd
    WHERE EXISTS 
    (
      SELECT 1 FROM inserted AS i 
      WHERE i.DocumentDistDocID = dd.DocumentDistDocID
      AND i.DocumentDistSourceID = dd.DocumentDistSourceID
    )
    GROUP BY DocumentDistDocID, DocumentDistSourceID
    HAVING COUNT(*) > 1
  )
  AND EXISTS
  (
    SELECT 1 FROM inserted 
    WHERE DocumentDistSourceID NOT IN 
    (
      SELECT DocumentSourceID FROM dbo.DocumentSource 
      WHERE DocumentSourceName IN 
      ('eBinder','eBinder - West Division',
       'Consolidated e-Binder','MA Consolidated eBinder')
    )
  )
  BEGIN
    ROLLBACK TRANSACTION;
  END
END

I think I have your logic correct; please test on a restored backup or dev system.

You might consider implementing this as an INSTEAD OF trigger - this allows you to either insert or not depending on your business logic (or only insert the rows that don't violate your constraints), instead of always inserting and then sometimes rolling back.

You might also consider implementing a unique constraint (or maybe a filtered unique index or an indexed view, if the duplicates only need to be enforced on a subset) if the primary purpose of this trigger is to prevent duplicates.

Related Question