Solved

Update Trigger

Posted on 2010-09-14
9
301 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Data Field - SQL 11 40
average of calculation (TSQL) 4 33
Comparison query - 4 columns 9 42
Converting Stored Procedure to SQL Statement 5 36
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

740 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