April 16, 2008 at 2:06 pm
I am having a problem getting an insert trigger to work as desired. Here is what I am trying to accomplish: when rows are inserted into a table on 1 database, I want to insert them to a table in another database, BUT only when a column in the original table has certain values. If it does not have one of those values, the row should not be copied to the other table. Also, it should not copy rows that have already been copied.
The main problem I'm encountering is how to limit this copy to just the new row that is being inserted and not the rows that were already in there and already copied. The trigger runs, but it is looking at all rows as opposed to just the new row being inserted. How do I account for this?
Here is my trigger (real database and table names replaced):
CREATE TRIGGER timetransfer ON db.tbl1
FOR INSERT
AS insert into db..tbl2 select dispatch_number,stop_number,e_datime,
case when e_type='arrived-occurred' then 1
when e_type='departed-occurred' then 2
else 99 end,'0',0 from db.tbl1
where e_type in('arrived-occurred','departed-occurred')
April 16, 2008 at 2:17 pm
You need to use the Inserted virtual table to only affect the rows that were just inserted.
as in
[font="Courier New"]
CREATE TRIGGER timetransfer ON db.tbl1
FOR
INSERT
AS
INSERT INTO db..tbl2
SELECT dispatch_number,stop_number,e_datime,
CASE WHEN e_type='arrived-occurred' THEN 1
WHEN e_type='departed-occurred' THEN 2
ELSE 99 END,'0',0
FROM Inserted--<--Change happens here
WHERE e_type IN('arrived-occurred','departed-occurred')
[/font]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 2:32 pm
Thanks, that was it! This is my 1st post and wish I would have come here before 2+ hours of Google searching.
So the rows in the inserted table are cleared once the next insert statement comes along?
April 16, 2008 at 2:37 pm
Correct. For all intents and purposes - it exists only within the confines of that create trigger statement.
Inserted (the new values) and deleted (the old values) are "virtual tables" tracking the changes being made. They are accessible only through triggers in SQL Server 2000.
Read up on Triggers in BOL - they explain a lot more about them.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply