Update data during insert in a Trigger and then insert

Question

I have situation where, I have 10 columns in a table. I am inserting 5 columns from a program and for 5 columns, I want to create a trigger. Is it possible? And if yes, How do I do that?

col1 col2 col3 col4 col5 col6 col7 col8 col9 col10

CREATE TRIGGER Fill_Trigger
   ON  DB 
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    DECLARE @COL6 INT
    DECLARE @COL7 INT -- and so on.


   SET @COL6 = (SELECT COL1 FROM inserted) * (SELECT COL2 FROM inserted)
   SET @COL7 = (SELECT COL3 FROM inserted) * (SELECT COL4 FROM inserted)

   --Is it possible to do the following?
  UPDATE inserted SET COL6 = @COL6
  UPDATE inserted SET COL7 = @COL7
END
GO

gbn gbn · Accepted Answer · 2013-05-21T10:04:14

You shouldn't use locsal variables in a trigger because it makes it "single row only"

Also, I assume you have a primary key called KeyColumn. And your table is called "DB".

CREATE TRIGGER Fill_Trigger
   ON DB 
   AFTER INSERT
AS 
BEGIN
   SET NOCOUNT ON;

   UPDATE DB
   SET
       Col6 = I.COL1 * COL2,
       Col7 = I.COL3 * COL4
   FROM
       DB
       JOIN
       INSERTED I ON DB.KeyColumn = I.KeyColumn;
END
GO

However, it some columns are based on other columns, I would use COMPUTED columns and not have a trigger

CREATE TABLE MyTable (
    KeyColumn ...,
    Col1 ...,
    Col2 ...,
    Col3 ...,
    Col4 ...,
    Col5 ...,

    Col6 AS Col1 * Col2,
    Col7 AS Col3 * Col4,
    Col8 AS ...

    );

Update data during insert in a Trigger and then insert

1 Answers