July 5, 2010 at 10:35 am
Hi I currently have an insert trigger that looks like this.
ALTER TRIGGER
[dbo].[POP_HEADER_LOGS] ON [dbo].[t_curve]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @REL_HEADER_REGION_ID INT
DECLARE @F_ID INT
SELECT DISTINCT @REL_HEADER_REGION_ID = REL_HEADER_REGION_ID
FROM TEAM.DBO.REL_HEADER_REGION
WHERE (REGION_ID = 1) AND (ODM_WELL_ID = (SELECT top(1) F_WELLID FROM inserted))
SELECT @F_ID = F_ID
FROM inserted
INSERT INTO TEAM.DBO.HEADER_LOGS
(REL_HEADER_REGION_ID,F_ID)
VALUES(@REL_HEADER_REGION_ID,@F_ID)
END
This works when I insert one row but causes and error when inserting many rows. Can you please help me on this, I have been trying to work it out for ages.
Many Thanks,
Oliver
July 5, 2010 at 10:49 am
here's my best guess, but i'd really need the actual table definitions and sample data, along with expected output/example to really give a decent answer.
ALTER TRIGGER
[dbo].[POP_HEADER_LOGS] ON [dbo].[t_curve]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO TEAM.DBO.HEADER_LOGS
(REL_HEADER_REGION_ID,F_ID)
SELECT REL_HEADER_REGION_ID,F_WELLID
FROM (
SELECT
ROW_NUMBER() OVER
ORDER BY (PARTITION BY INSERTED.F_WELLID ORDER BY INSERTED.F_WELLID) AS RW,
REG.REL_HEADER_REGION_ID,
INSERTED.F_WELLID
FROM INSERTED
INNER JOIN TEAM.DBO.REL_HEADER_REGION REG
ON REG.ODM_WELL_ID INSERTED.F_WELLID
WHERE (REG.REGION_ID = 1) ) MYALIAS
WHERE MYALIAS.RW = 1
END
Lowell
July 5, 2010 at 10:55 am
Thank you for the quick response I will give this a go and let you know.
Cheers,
Oliver
July 5, 2010 at 11:14 am
The key thing to remember is that a multi-row insert is a single transaction and your trigger will fire only once for the set of rows, not once for each row. That means your trigger will have to be able to retrieve the entire set of rows it needs to process.
You might also look up the OUTPUT clause in Books Online. Rather than use a trigger, you could use it to trap into a temp table the set of rows you are inserting , then use that set as input to any further logic.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 5, 2010 at 11:40 am
there may be more to it, but based on what you posted, it looks to me like the table TEAM.DBO.HEADER_LOGS could be replaced by a view which gets the rollup of the data you want without the overhead/complexity of a trigger.
Lowell
July 5, 2010 at 11:54 am
Thanks for your comments.
The issue I am having with this is that when I append many rows to the table with the trigger on I recieve and error, however when I enter it one at a time I dont get the error.
When I bulk append data in through using MS as a front end the trigger works fine whereas through SSMS with T-SQL I get the error.
Thanks for your help on this, I have tried to understand the trigger but I found it hard to break down and understand what was going on. I will try to look at options with views. Its a tricky one this one.
Many thanks,
Oliver
July 5, 2010 at 12:21 pm
Could you please tell us what error you are getting? More information may be needed such as what your table schema are, what data is producing the errror, and what constraints exist. But the exact error would be a starting point.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply