CODEWITHSUNDEEP
sql-servertriggers
Sufo
Sufo

Reputation: 27

Using Calculated Fields with SQL Triggers

I'm trying to write an SQL Trigger with the following approximate form:

CREATE TRIGGER TableABSync ON TableA
FOR INSERT AS

INSERT INTO TableB
        (col1, col2, col3, col4)
    SELECT
        PK_ColA, ColB, ColC - ColD, ColE * TableB.Col3
    FROM inserted

So I'd like to use the newly generated Col3 to calculate Col4. Will I have to do this in stages?

Cheers

Upvotes: 0

Views: 132

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

The issue has nothing to do with triggers or the insert - you can't reference a calculated value in the SELECT clause from within the same SELECT clause.

Maybe:

CREATE TRIGGER TableABSync ON TableA
FOR INSERT AS

INSERT INTO TableB
        (col1, col2, col3, col4)
    SELECT
        PK_ColA, ColB, ColC - ColD as Col3, ColE * .Col3
    FROM
       (
          SELECT PK_ColA, ColB, ColC - ColD as Col3,ColE
          FROM inserted
       ) t

Upvotes: 0

NG.
NG.

Reputation: 6063

Why dont you directly use something like : 

CREATE TRIGGER TableABSync ON TableA
FOR INSERT AS

INSERT INTO TableB
        (col1, col2, col3, col4)
    SELECT
        PK_ColA, ColB, ColC - ColD, ColE *( TableB.ColC - TableB.ColD)
    FROM inserted

Upvotes: 1

Related Questions