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 · Accepted Answer · 2013-05-21 10:04:14Z

1

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

    );

answered May 21, 2013 at 10:04

gbn

435k84 gold badges602 silver badges690 bronze badges

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

7 Comments

Jasim Khan Afridi Over a year ago

I didnt get this part "You shouldn't use locsal variables in a trigger because it makes it "single row only"", can you explain? Because I am doing number of things in this trigger, and I need to use variables. I am calling CLR function also.

gbn Over a year ago

If you insert 2+ rows, then you don't know what value the variable will get. It will come from any row. And this sounds way too complex for a trigger.

Jasim Khan Afridi Over a year ago

My sample in OP was just an example. I have a little complex calculation here.

gbn Over a year ago

Only if you INSERT one row.

Jasim Khan Afridi Over a year ago

Yes, it is 1 row at a time.. Thanks a lot for help.

|

Collectives™ on Stack Overflow

Update data during insert in a Trigger and then insert

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related