Insert/Update Trigger not working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert/Update Trigger not working

Author

Topic

Shilpa22
Starting Member

37 Posts

Posted - 2010-06-09 : 05:58:35

Hi I have the below trigger which will fire when an insert operation happens on the Table. This Trigger has to update another Table, which is not happening. Pls help me out,

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [StatsTrigger]
ON [dbo].[Log]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for trigger here
Declare @Ed as int
Set @Ed = (Select Ed from inserted)

if(@Ed = 1000)
Begin
Declare @Ld as int
Set @Ld = (select Ld from Inserted)

Declare @Cn as nvarchar(64)
Set @Cn = (select c.Cn
from log l
inner join CaLog cl on l.Ld = cl.Ld
inner join Ca c on cl.CaId = c.Caid where l.Ld = @Ld )

Declare @ReportSourceId as Varchar(50)
Set @ReportSourceId = (Select [Message] from inserted)

DECLARE @TotalClickRate as int
DECLARE @UsageStat as int

Set @TotalClickRate = (Select count(Ld) from EpLog..log where Ed = 400
and datediff(day, timestamp, getutcdate()) <= 30)

Set @UsageStat = (Select (count(l.Ld)*100)/@TotalClickRate as ClickRate
From
EpLog..log l inner join EpLog..CaLog cl
on l.Ld = cl.Ld
inner join EpLog..Ca c on c.Caid = cl.Caid
where l.[Message] = Cast(@ReportSourceId as varchar(50))and c.Cn = @Cn
and datediff(day, timestamp, getutcdate()) <= 30)

Declare @ReportUniqueId as int
Set @ReportUniqueId = (Select r.uniqueId
From
website..Report r inner join
website..Source s on r.Source = s.ID where sourceId = @ReportSourceId and r.source =
(CASE @Cn
WHEN 'BOBJ REPORT CLICK' THEN 1
WHEN 'HYPERLINK REPORT CLICK' THEN 4
WHEN 'D3 REPORT CLICK' THEN 2
END))

exec website..proc_update_UsageStat @ReportUniqueId, @UsageStat
End
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Thanks in Advance
Shilpa

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-09 : 08:33:28

As far as I cans ee the trigger only runs when @Ed = 1000...are you sure Ed from the inserted table is 1000? Also be aware that Set @Ed = (Select Ed from inserted) will wail if you insert/update mre than one row.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-09 : 09:29:16

quote:
Originally posted by Shilpa22

Declare @Ed as int
Set @Ed = (Select Ed from inserted)

What's going to happen if there's more than one row in the inserted table?

--
Gail Shaw
SQL Server MVP

Shilpa22
Starting Member

37 Posts

Posted - 2010-06-10 : 01:58:02

Any suggestions/ideas please !!!

Thanks in Advance
Shilpa

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-10 : 02:34:55

quote:
Originally posted by Lumbago
...are you sure Ed from the inserted table is 1000?

Is this not a suggestion? What kind of debugging have you done? PRINT statements inside the trigger will get displayed in management studio when the trigger is fired...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources