SQL TRIGGER handling multiple INSERTED rows

Question

I have an UPDATE which looks like this:

UPDATE STOR SET
    STOR.BLOC1 = T.BLOC1,
    STOR.BLOC2 = T.BLOC2,
    STOR.BLOC3 = T.BLOC3
FROM BLDG B INNER JOIN STOR S
ON S.B_ID = B.B_ID
CROSS APPLY dbo.INVENTORIZE(B.B_ID) AS T;

And a TRIGGER which is meant to insert a row (like a log) for each update above.

CREATE TRIGGER trgrCYCLE
ON STOR
FOR UPDATE
AS
DECLARE @BLDG int, @ACT varchar(4), @QTY decimal(3,1);

SET @BLDG = (SELECT B_ID FROM inserted)
SET @QTY= (SELECT BLOC1 FROM inserted)
SET @ACT = CASE WHEN @QTY < 0 THEN 'SELL'
            ELSE 'BUY' END

INSERT INTO INVT VALUES (CURRENT_TIMESTAMP, @BLDG, @ACT, @QTY)

I have two problems for which I need assistance:

the fact of the inserted pseudo table having multiple rows is returning an error ("Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."). I haven't been able to transport answers in similar questions to my case.
how to have the trigger work for n number of BLOC columns (BLOC1, BLOC2, BLOC3, BLOCn...)

You need to better define 2. What does "work for n number of BLOC columns" mean? Are there multiple columns in the destination table? Are you trying to insert a new row per BLOC column? Right now the requirement is very loosy-goosy. — Aaron Bertrand
– Aaron Bertrand, Commented Feb 5, 2014 at 22:13

Aaron Bertrand · Accepted Answer · 2014-02-05 22:33:45Z

3

For the first question, you can't use variables to store values from multiple rows. Insert as a set.

INSERT dbo.INVT -- schema prefix!
  (column_list!!!)
  SELECT CURRENT_TIMESTAMP, B_ID, 
    CASE WHEN BLOC1 < 0 THEN 'SELL' ELSE 'BUY' END, BLOC1
  FROM inserted;

For the second question, the easiest way is probably an INSERT statement per BLOCn.

INSERT dbo.INVT -- schema prefix!
  (column_list!!!)
  SELECT CURRENT_TIMESTAMP, B_ID, 
    CASE WHEN BLOC2 < 0 THEN 'SELL' ELSE 'BUY' END, BLOC2
  FROM inserted;

INSERT dbo.INVT -- schema prefix!
  (column_list!!!)
  SELECT CURRENT_TIMESTAMP, B_ID, 
    CASE WHEN BLOC3 < 0 THEN 'SELL' ELSE 'BUY' END, BLOC3
  FROM inserted;

edited Feb 5, 2014 at 22:33

answered Feb 5, 2014 at 22:10

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

marc_s Over a year ago

@GordonLinoff: Aaron's got really quick fingers :-)

greener Over a year ago

Thx! the UPDATE has several BLOC columns and INVT needs to hold a record for each, e.g. an insert for each BLOC for each inserted

greener Over a year ago

Follow-up: when the BLOCn value is 0 or null, it shouldn't INSERT into INVT. I tried this IF ISNULL((SELECT BLOC2 FROM inserted),0) > 0 BEGIN ... END but no luck.

Aaron Bertrand Over a year ago

@greener uh, just add WHERE COALESCE(BLOC2,0) <> 0

Collectives™ on Stack Overflow

SQL TRIGGER handling multiple INSERTED rows

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related