Solved

Update Trigger

Posted on 2010-09-14
9
303 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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