[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

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?
0
al4629740
Asked:
al4629740
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
al4629740Author Commented:
Could you show me using the information I gave you?
0
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
al4629740Author Commented:
yes it does

ID
0
 
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
 
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now