Solved

Update Trigger

Posted on 2010-09-14
9
305 Views
Last Modified: 2012-05-10
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
Comment
Question by:pmac38CDS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 33673478
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
 
LVL 1

Author Comment

by:pmac38CDS
ID: 33673745
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 33673802
you need to swap out your primary key column(s) on your table in that join statement.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 1

Author Comment

by:pmac38CDS
ID: 33673907
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 33673922
post your real code and I'll sort it out.
0
 
LVL 1

Author Comment

by:pmac38CDS
ID: 33673962
-- ================================================
-- 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
 
LVL 1

Author Comment

by:pmac38CDS
ID: 33676641
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
 
LVL 1

Author Comment

by:pmac38CDS
ID: 33683512
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
 
LVL 1

Author Closing Comment

by:pmac38CDS
ID: 33684906
I had to modify the trigger to be able to execute it correctly.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question