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?
al4629740Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]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

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
al4629740Author Commented:
Could you show me using the information I gave you?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Guy Hengel [angelIII / a3]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
 
al4629740Author Commented:
yes it does

ID
0
 
aplusexpertCommented:
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.

All Courses

From novice to tech pro — start learning today.