SQL Trigger not working on update statement

BSRK

hi i am having a table with 3 columns (a,b,c). i have written a trigger for INSERT / UPDATE as c=a+b this is working fine as long as i enter/update each row by row manually. but, when i use any update statement for a set of rows, the trigger is firing only for the last row. can anyone please help? Thanks

Eric Dahlvang

If the 'c' column is only a calculation of a+b, why not just use **([a] + [b])** as a formula? Anyway, you could also use INSTEAD OF triggers:

CREATE TRIGGER InsteadUpdateTrigger on tblTable
INSTEAD OF UPDATE
AS
BEGIN
UPDATE tblTable
SET a=i.a,b=i.b,c=i.a+i.b
FROM INSERTED i
WHERE tblTable.IDField = i.IDField
END

CREATE TRIGGER InsteadInsertTrigger on tblTable
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO tblTable
SELECT a,b,a+b
FROM INSERTED
END

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

Chris Meech

Is there a reason for not using a VIEW on this table? The table would have columns a and b. The view would have columns a, b, and c where c is defined as (a+b).

Chris Meech I am Canadian. [heard in a local bar] Nobody likes jerks. [espeir] The zen of the soapbox is hard to attain...[Jörgen Sigvardsson] I wish I could remember what it was like to only have a short term memory.[David Kentley]

Eric Dahlvang

Or, just give the c field a formula of ([a] + [b]):

CREATE TABLE [tblTable] (
[IDField] [int] IDENTITY (1, 1) NOT NULL ,
[a] [int] NULL ,
[b] [int] NULL ,
**[c] AS ([a] + [b])** ,
CONSTRAINT [PK_tblIDField] PRIMARY KEY CLUSTERED
(
[IDField]
) ON [PRIMARY]
) ON [PRIMARY]

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters