Go Premium for a chance to win a PS4. Enter to Win

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

Update Trigger

I have a database table containing for example 6 columns Column1,Column2,Column3,Column4,Column5 and Count. The Count column stores a count of Column1-5 that are not null. So if Columns1 and 3 are not null for a particular row it will store 2. I was wondering how can I go about writing a trigger that updates the count column whenever Columns 1 through 5 are updated.

Let me know if you need anything else from my end.
Aditya
0
pmac38CDS
Asked:
pmac38CDS
  • 6
  • 3
1 Solution
 
chapmandewCommented:
create trigger mytriggername on tablename
after  update
as
begin
if update(col1) or update(col2) or update(col3) or update(col4) or update(col5)
update t
set total = isnull(col1,0) +isnull(col2,0) +isnull(col3,0) +isnull(col4,0) +isnull(col5,0)
from tablename t
join inserted i on t.idcol = i.idcol
end
0
 
pmac38CDSAuthor Commented:
This is the error I get when I try to create the trigger
Msg 207, Level 16, State 1, Procedure Update_GSFund_Count, Line 18
Invalid column name 'idcol'.
Msg 207, Level 16, State 1, Procedure Update_GSFund_Count, Line 18
Invalid column name 'idcol'
0
 
chapmandewCommented:
you need to swap out your primary key column(s) on your table in that join statement.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
pmac38CDSAuthor Commented:
This is what I get now
Ambiguous column name 'column1"
It does this for each of the 15 columns I have

Any ideas ?
Aditya
0
 
chapmandewCommented:
post your real code and I'll sort it out.
0
 
pmac38CDSAuthor Commented:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
CREATE TRIGGER Update_GSFund_Count on dbo.Cyclone_PlanProfile
after  update
AS
BEGIN
if update(gSFund1) or update(gSFund2) or update(gSFund3) or update(gSFund4) or update(gSFund5) or
update(gSFund6) or update(gSFund7) or update(gSFund8) or update(gSFund9) or update(gSFund10) or
update(gSFund11) or update(gSFund12) or update(gSFund13) or update(gSFund14) or update(gSFund15)
update t
set gSFund_Count = isnull(gSFund1,0) +isnull(gSFund2,0) +isnull(gSFund3,0) +isnull(gSFund4,0) +isnull(gSFund5,0)+
isnull(gSFund6,0) +isnull(gSFund7,0) +isnull(gSFund8,0) +isnull(gSFund9,0) +isnull(gSFund10,0)+
isnull(gSFund11,0) +isnull(gSFund12,0) +isnull(gSFund13,0) +isnull(gSFund14,0) +isnull(gSFund15,0)
from dbo.Cyclone_PlanProfile t
join inserted i on t.cyProfileId = i.cyProfileId
END
GO
0
 
pmac38CDSAuthor Commented:
Couple of other things
the gSFund1 through 15 are varchar columns. If the value is not null then it should return a 1 which gets added to the total(in this case if it is not null I guess it is returning the actual value). So for example if 6 out of the 15 columns are not null the gSFund_Count(this variable is an integer) variable should store the value 6.
0
 
pmac38CDSAuthor Commented:
I have to modify the trigger to be able to execute it
ALTER TRIGGER [dbo].[Update_GSFund_Count] on [dbo].[Cyclone_PlanProfile]
after  update
AS
DECLARE @gSFund1Count int
if update(gSFund1)or update(gSFund2) or update(gSFund3) or update(gSFund4) or update(gSFund5) or
update(gSFund6) or update(gSFund7) or update(gSFund8) or update(gSFund9) or update(gSFund10) or
update(gSFund11) or update(gSFund12) or update(gSFund13) or update(gSFund14) or update(gSFund15)
BEGIN
   update t
      set gSFund_Count = isnull(i.gSFund1,0) +isnull(i.gSFund2,0) +isnull(i.gSFund3,0) +isnull(i.gSFund4,0) +isnull(i.gSFund5,0)+
isnull(i.gSFund6,0) +isnull(i.gSFund7,0) +isnull(i.gSFund8,0) +isnull(i.gSFund9,0) +isnull(i.gSFund10,0)+
isnull(i.gSFund11,0) +isnull(i.gSFund12,0) +isnull(i.gSFund13,0) +isnull(i.gSFund14,0) +isnull(i.gSFund15,0)
     from dbo.Cyclone_PlanProfile t
     join inserted i
       on t.cyProfileId = i.cyProfileId
END
GO
However the problem I see is the following
the gSFund1 through 15 are varchar columns. If the value is not null then it should return a 1 which gets added to the total(in this case if it is not null I guess it is returning the actual value). So for example if 6 out of the 15 columns are not null the gSFund_Count(this variable is an integer) variable should store the value 6.
How do I modify the trigger to achieve this?
0
 
pmac38CDSAuthor Commented:
I had to modify the trigger to be able to execute it correctly.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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