# calculation for opposite column

I have two columns in my SQL Express Table.  If Age column = 18 or above, then the Code column should have the number in it changed to 3.

How can I automatically make the Code column do this?
###### Who is Participating?

Billing EngineerCommented:
check this out:
``````create trigger trg_set_code
as
begin
update t
set code_field = 3
from yourtable t
join inserted i
on i.primary_key_field = t-primary_key_field
and i.age >= 18

end
``````
0

Billing EngineerCommented:
0

Author Commented:
Could you show me using the information I gave you?
0

Billing EngineerCommented:
hmmm. actually, you want to change the value to 3 only and once the value of age changes to 18 or higher?
then, you need a trigger...
does your table have a primary key?
0

Author Commented:
yes it does

ID
0

Commented:
Hi,

You can create a trigger on that table to update the value. Suppose we have created a table "Table1" as:

[[
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[RawID] [int] IDENTITY(1,1) NOT NULL,
[Age] [int] NULL,
[code] [int] NULL
) ON [PRIMARY]
GO

]]

Now we can create a trigger that will do the update in the "Code" column whenever Age filed value changes. The trigger syntax is as below:

[[

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER tr_set_code_value
ON  dbo.Table1
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Age int,
@RawID int

IF(UPDATE(Age))
BEGIN
SELECT      @Age = Age,
@RawID = RawID
FROM INSERTED

IF (@Age >= 18)
BEGIN
UPDATE      Table1
SET Table1.Code = 3
WHERE      Table1.RawID = @RawID
END
END

END
GO

]]

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.