CODEWITHSUNDEEP
sql-servert-sql
greener
greener

Reputation: 5069

SQL TRIGGER handling multiple INSERTED rows

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:

  1. 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.

  2. how to have the trigger work for n number of BLOC columns (BLOC1, BLOC2, BLOC3, BLOCn...)

Upvotes: 0

Views: 83

Answers (1)

anon
anon

Reputation:

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;

Upvotes: 3

Related Questions