CODEWITHSUNDEEP
sql-serversql-server-2005
firas
firas

Reputation: 23

adding data in column depending on another columns in same table

Dear i already create one table for employee information with ASP.net interface now i show add one operation in my table as below,

i have four columns( WorkDatey,WorkDatem,WorkDated,absentday) all the columns from integer type in table name employee. now i should add new column (realworkdays) and the result here should be automatic save after entering previous data as example:

    WorkDatey= 2011 ,
    WorkDatem= 2  ,
    WorkDated=14 ,
    absentday=6

the operation should be : (2011*365)+(2*30)+(14)-(6) and result will store in column (realworkdays).

i already try it as trigger but i have some thing wrong:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER daysResult
   ON  [dbo].[employee]
   AFTER insert
As
DECLARE @realworkdays int

BEGIN
    SET NOCOUNT ON;


select WorkDatey,WorkDatem,WorkDated,absent from [dbo].[employee]
@realworkdays= (workdatey*350)+(workdatem*29)+(workdated)-(absent)

insert into [dbo].[employee] (totaldays)values (@realworkdays)

END
GO

Upvotes: 2

Views: 1157

Answers (2)

Andriy M
Andriy M

Reputation: 77657

You are supposed to update the inserted records, not insert new ones.

So, it should be the UPDATE statement, and you can do both the calculations and the updates in one go.

CREATE TRIGGER daysResult
ON [dbo].[employee]
AFTER insert
AS
BEGIN
  UPDATE employee
  SET realworkdays = (inserted.workdatey*350)+(inserted.workdatem*29)+
                     (inserted.workdated)-(inserted.absent)
  FROM inserted
  WHERE employee.ID = inserted.ID
END

Still it would be better to use a computed column, as @The Scrum Meister has suggested.

Upvotes: 0

The Scrum Meister
The Scrum Meister

Reputation: 30111

Since you are using sql server 2005, i would suggest you use a computed column

ALTER TABLE employee DROP COLUMN realworkdays;
ALTER TABLE employee ADD COLUMN realworkdays AS (
    (WorkDatey * 365) +
    (WorkDatem * 30) +
    (WorkDated) -
    (absentday)
)

You can add the PERSISTED so it will store the data, optionally indexing it.

On a side note, your trigger is inserted a new row, instead of updating the value of the current row.

Upvotes: 4

Related Questions