Loading ...

How to set a particular column on behalf of others columns value | CodeAsp.Net

How to set a particular column on behalf of others columns value

 /5
0 (0votes)

In this blog i will explain how to set a particular column on behalf of others columns value .I have shown two examples for that.You can make that column as below

CREATE TABLE [dbo].[Test_Column_Five]

(

[col1] [INT] NOT NULL CONSTRAINT [DF_Test_Column_Five_col1] DEFAULT ((0)),

[col2] [INT] NOT NULL CONSTRAINT [DF_Test_Column_Five_col2] DEFAULT ((0)),

[col3] [INT] NOT NULL CONSTRAINT [DF_Test_Column_Five_col3] DEFAULT ((0)),

[col4] [INT] NOT NULL CONSTRAINT [DF_Test_Column_Five_col4] DEFAULT ((0)),

[col5] AS CAST([col1] + [col2]+ [col3] + [col4] AS [INT])

)

GO

INSERT INTO [Test_Column_Five]

([col1],[col2],[col3],[col4])

VALUES

(1,2,3,4)

GO

--To test that it has performed at column 5

SELECT * FROM [Test_Column_Five]

GO

--Output

col1 col2 col3 col4 col5

———– ———– ———– ———– ———–

1 2 3 4 10

as u have seen u have get the desired result u want ie 1+2+3+4=10

second example:

GO

DROP TABLE [Test_Column_Five]

GO

CREATE TABLE [dbo].[Test_Column_Five]

(

id INT IDENTITY,

[col1] [INT] NOT NULL CONSTRAINT [DF_Test_Column_Five_col1] DEFAULT ((0)),

[col2] [INT] NOT NULL CONSTRAINT [DF_Test_Column_Five_col2] DEFAULT ((0)),

[col3] [INT] NOT NULL CONSTRAINT [DF_Test_Column_Five_col3] DEFAULT ((0)),

[col4] [INT] NOT NULL CONSTRAINT [DF_Test_Column_Five_col4] DEFAULT ((0)),

[col5] [INT] NOT NULL CONSTRAINT [DF_Test_Column_Five_col5] DEFAULT ((0))

)

GO

CREATE TRIGGER [INSERT_UPDATE_Test_Colmn_Five]

ON [Test_Column_Five]

AFTER

INSERT,UPDATE

AS

BEGIN

UPDATE [Test_Column_Five]

SET [Test_Column_Five].[col5]=(INSERTed.[col1] +INSERTed.[col2] +INSERTed.[col3] +INSERTed.[col4] )

FROM [Test_Column_Five]

INNER JOIN INSERTed

ON [Test_Column_Five].id=[Test_Column_Five].id

END

GO

INSERT INTO [Test_Column_Five]

([col1],[col2],[col3],[col4])

VALUES

(1,2,3,4)

GO

--To test that it has performed at column 5

SELECT * FROM [Test_Column_Five]

GO

--Output

id col1 col2 col3 col4 col5

———– ———– ———– ———– ———– ———–

1 1 2 3 4 10

--Another test that for update

UPDATE [Test_Column_Five] SET col1=20 where id=1

GO

--To test that it has performed

SELECT * FROM [Test_Column_Five]

GO

--Output

id col1 col2 col3 col4 col5

———– ———– ———– ———– ———– ———–

1 20 2 3 4 29

Happy Reading.!

 

Comments (no comments yet)

Top Posts