February 27, 2012 at 3:10 am
I have a Table named DATAFEED in my DB that receives info every minute through a JAVA app and I need that info inserted to another table, so I created a AFTER INSERT trigger in DATAFEED to move this info to the other table, but the trigger never fires! Its possible that the JAVA app inserts the data on DATAFEED without using insert?
February 27, 2012 at 3:33 am
lucchesediego (2/27/2012)
I have a Table named DATAFEED in my DB that receives info every minute through a JAVA app and I need that info inserted to another table, so I created a AFTER INSERT trigger in DATAFEED to move this info to the other table, but the trigger never fires! Its possible that the JAVA app inserts the data on DATAFEED without using insert?
Without seeing the definition of the trigger, i'd suggest firing up SQL profiler and have a ganders at what sql is being generated for the insert - there will be an INSERT statement being generated for this data. This should also give you a clue as to why the trigger isn't firing.
February 27, 2012 at 3:44 am
why are u using a trigger , there is a performance hit for highly transactional tables where the trigger will be fired frequently. Have you considered simply calling single proc with a transaction which inserts into both tables simultaneously.
Have u checked if the base table gets records inserted .
February 27, 2012 at 3:52 am
Im a total noob at SQL SERVER im using a Trigger because I need to know when some data is added to the DATAFEED table so it can be inserted on the other table. And yes the table is getting records inserted
February 27, 2012 at 4:18 am
February 27, 2012 at 4:23 am
USE [DEVEL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRASPASO_DESARROLLO2]
ON [dbo].[DATAFEED]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @Ric varchar(50), @DATE datetime ,@CIERRE float
select @Ric = RIC, @DATE = FECHA, @CIERRE = CIERRE from inserted
if(SELECT COUNT(*) FROM EUS.DESARROLLO.DBO.F_RIC_ULTIMO_PRECIO WHERE RIC = @Ric) > 0
BEGIN
UPDATE EUS.DESARROLLO.DBO.F_RIC_ULTIMO_PRECIO SET FECHA = @DATE,
@CIERRE = CIERRE WHERE RIC = @Ric
END
ELSE
BEGIN
INSERT INTO EUS.DESARROLLO.DBO.F_RIC_ULTIMO_PRECIO (RIC,ULTIMO_PRECIO,FX_ULTIMO_PRECIO)
VALUES (@RIC,@CIERRE,@DATE)
END
END
February 27, 2012 at 5:35 am
your trigger will only handle one row; so if the applicaiton inserts or updates multiple rows, you will not get all teh changes.
something like this is probably pretty close to what you need, but I'd consider moving the logic to a scheduled job instead of a trigger.
a trigger that touches a different server suffers from an inherent problem...if the target server+database is unavailable whether network issues, permissions, application timeouts...., the data that WAS going to be inserted or updated will raise an error, and the trigger will rollback.
as a result, you get situations where you *know* data was inserted, but you cannot find it anywhere.
it's not obvious when a trigger rolls back that an error was raised; a lot of applications don't handle it gracefully`
ALTER TRIGGER [dbo].[TRASPASO_DESARROLLO2]
ON [dbo].[DATAFEED]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
BEGIN
UPDATE REMSERVER
SET FECHA = DATE,
CIERRE = CIERRE
FROM INSERTED
LEFT OUTER JOIN EUS.DESARROLLO.DBO.F_RIC_ULTIMO_PRECIO REMSERVER
ON INSERTED.RIC = REMSERVER.RIC
INSERT INTO EUS.DESARROLLO.DBO.F_RIC_ULTIMO_PRECIO
(RIC,ULTIMO_PRECIO,FX_ULTIMO_PRECIO)
SELECT
RIC,CIERRE,DATE
FROM INSERTED
LEFT OUTER JOIN EUS.DESARROLLO.DBO.F_RIC_ULTIMO_PRECIO REMSERVER
ON INSERTED.RIC = REMSERVER.RIC
WHERE REMSERVER.RIC IS NULL
END
END
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply