I am curious if the OUTPUT clause can be used conditionally based on the UPDATE that creates the insert.
For example, with a boolean checkbox...
UPDATE myTable1 SET BinCheckBox = CASE WHEN BinCheckBox = '1' THEN 1 ELSE 0 END
OUTPUT
inserted.col1
inserted.col2
INTO
myTable2
WHERE myTable1.id = myFormField.id
...now, this part works fine. But I only want to use OUTPUT if the row in myTable1 is actually updated to myTable1.BinCheckBox = 1.
I had this working with a database Trigger, but I would like it all to happen in my statement if possible. Psuedo code like...
UPDATE myTable 1 SET BinCheckBox = CASE WHEN BinCheckBox = '1' THEN 1 ELSE 0 END...AND IF BinCheckBox updated to 1, then OUTPUT...
Thank you for any advice
UPDATE / ANSWER
So instead of using OUTPUT, I am using INSERT INTO myTable2 SELECT, and a DELETE myTable2 FROM ... , to handle removing rows. Like so...
UPDATE myTable1 SET BinCheckBox = CASE WHEN BinCheckBox = '1' THEN 1 ELSE 0 END
INSERT INTO myTable2 (col1,col2)
SELECT col1,col2
FROM myTable1 m1
WHERE (BinCheckBox = 1)
AND NOT EXISTS (SELECT m2.id FROM myTable2 m2
WHERE (m2.id=m1.id));
DELETE m2 FROM myTable2
JOIN myTable1 m1 on m2.id=m1.id
WHERE m1.id=,y.id AND m1.BinCheckBox = 0;
This seems to work great.