?
Solved

Update Trigger

Posted on 2010-09-14
9
Medium Priority
?
309 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 1500 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

719 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