Update Trigger Problem - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Update Trigger Problem

Author

Topic

petersrj
Starting Member

20 Posts

Posted - 2008-05-07 : 23:28:09

I am collecting data every minute and making 15 minute averages. I am using triggers to re-calculate the 15-minute average whenever data is inserted or updated in the 1-minute table. My trigger needs to be able to handle both INSERT and UPDATE as I can get data out of memory in which part would exist (UPDATE) and part would not (INSERT).

The problem I have is that the update portion of the code below triggers a downstream Update trigger, even if no data is updated. For example, in my TempValidCalc1Minute Table, I have a single record that does not exist in the ValidCalc1Minute and therefore that portion of the code should not create an update. This causes my downstream UPDATE trigger to fire when no records have been updated.

Thanks for your help.

An abbreviated form of my trigger looks like this:

CREATE TRIGGER [dbo].[Tr_InsValidCalc1MinData]
ON [dbo].[ValidRaw1Minute]
FOR Insert, Update
AS

.....

--Perform Update if the data exists otherwise do an insert
Update ValidCalc1Minute
Set Data1 = TempValidCalc1Minute.Data1
From ValidCalc1Minute, TempValidCalc1Minute
Where EXISTS
(Select * from TempValidCalc1Minute where ValidCalc1Minute.Timeframe = TempValidCalc1Minute.Timeframe)

-- Insert data that does not exist
Insert into ValidCalc1Minute
Select TimeFrame, Data1
from TempValidCalc1Minute
WHERE TimeFrame
NOT IN (Select TimeFrame from ValidCalc1Minute)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 04:02:31

You've created the trigger on ValidRaw1Minute table but your operations seem to check for records in TempValidCalc1Minute. Can you specify why you're doing like this? Shouldnt the comparison be done b/w ValidRaw1Minute & ValidCalc1Minute?

petersrj
Starting Member

20 Posts

Posted - 2008-05-08 : 11:31:17

Here is the order in which I am doing things:

1. A separate program inserts data into ValidRaw1Minute.

2. The new or updated records in ValidRaw1Minute are inserted into TempValidCalc1Minute. A stored procedure is also run to make calculations on this data.

3. Next, I either want to update the data in ValidCalc1Minute if it exists or else insert the records that don't.

All of that works well in that data is inserted as it needs to be and updated as it needs to be.

The problem is that I have a trigger on ValidCalc1Minute (for Update and Insert) that is getting called by the update portion of the code, even if no records are updated. If I have one brand new data point in the TempValidCalc1Minute, it should be inserted in ValidCalc1Minute. My trigger fires twice, once for the update and once for the insert.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 11:42:11

[code]CREATE TRIGGER [dbo].[Tr_InsValidCalc1MinData]
ON [dbo].[ValidRaw1Minute]
FOR Insert, Update
AS

INSERT INTO TempValidCalc1Minute
SELECT * FROM INSERTED
(replace * by your required columns if TempValidCalc1Minute is not of same structure as ValidRaw1Minute)

execute your sp here

.....

--Perform Update if the data exists otherwise do an insert
Update ValidCalc1Minute
Set Data1 = t.Data1
From ValidCalc1Minute v
INNER JOIN TempValidCalc1Minute t
ON v.Timeframe = t.Timeframe
INNER JOIN INSERTED i
ON i.FKCol=t.PKCol

-- Insert data that does not exist
Insert into ValidCalc1Minute
Select TimeFrame, Data1
from TempValidCalc1Minute t
INNER JOIN INSERTED i
ON i.FKCol=t.PKCol
LEFT JOIN ValidCalc1Minute v
ON v.TimeFrame=t.TimeFrame
WHERE v.TimeFrame IS NULL
GO
[/code]
PKCol & FKCol are columns that provide relation b/w ValidRaw1Minute & TempValidCalc1Minute.

petersrj
Starting Member

20 Posts

Posted - 2008-05-08 : 18:22:10

First of all, visakh16 thanks for all of your help.

I tried the changes you listed and it still did not work correctly. I have simplified the problem and am providing code that duplicates the problem. The code does the following:

1. Creates two tables: Valid and Temp. Valid has two records, one at 4:30 and one at 4:31. Temp has a record at 4:32.

2. Two triggers are created on the Valid Table: one for UPDATE and one for INSERT. They simply print when they were fired and how many rows were in the INSERTED table.

Next is the code which is to UPDATE the valid table if the data already exists or else insert the value. Given that the Temp file has one record which does not exists in the Valid Table, the UPDATE trigger should not run. When I run it, I get the following:

Update trigger fired on 0 Records at May 8 2008 5:11PM

(0 row(s) affected)
Insert trigger fired on 1 Records at May 8 2008 5:11PM

(1 row(s) affected)

Thanks again for your help. I can always trap for 0 records in the INSERTED table but that seems to be very inefficient.

****** Code to Create Tables and Triggers ******
CREATE TABLE [dbo].[Valid](
[TimeFrame] [datetime] NOT NULL,
[Data1] [float] NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[ValidInsert]
ON [dbo].[Valid]
FOR INSERT
AS
BEGIN
Declare @RC INT

Set @RC = (Select count(*) from inserted)

print 'Insert trigger fired on ' + Convert(nvarchar(2), @RC) + ' Records at ' + Convert(nvarchar(30), Getdate())
END
GO

CREATE TRIGGER [dbo].[ValidUpdate]
ON [dbo].[Valid]
FOR UPDATE
AS
BEGIN
Declare @RC INT

Set @RC = (Select count(*) from inserted)

print 'Update trigger fired on ' + Convert(nvarchar(2), @RC) + ' Records at ' + Convert(nvarchar(30), Getdate())
END
GO

CREATE TABLE [dbo].[Temp](
[TimeFrame] [datetime] NOT NULL,
[Data1] [float] NULL
) ON [PRIMARY]

GO

INSERT INTO Valid
Values ('5/8/2008 4:30:00 PM', '20')
INSERT INTO Valid
Values ('5/8/2008 4:31:00 PM', '30')

INSERT INTO Temp
Values ('5/8/2008 4:32:00 PM', '100')

****** Code to Demonstrate Problem ******
BEGIN TRAN T1

Update Valid
Set Data1 = T.Data1
From Valid V
INNER JOIN Temp T
ON V.Timeframe = T.Timeframe

INSERT into Valid
SELECT TimeFrame, Data1
FROM Temp
WHERE TimeFrame
NOT IN (SELECT TimeFrame FROM Valid)

ROLLBACK TRAN T1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources