inserting last record....
-
hey guys,
i insert into a table that has a trigger (FOR INSERT).
The problem is when i 'batch' insert only the last record is inserted...
i.e.
INSERT INTO table1 (field1,field2)
SELECT field3,field4 FROM table2
.....
and there is a trigger on table1
Any ideas???
thanx in advance...
TNT
SQLServer2000, XP
-
What does your trigger do?
-
Hi Antares686,
thanx for replying..
i pass an id to the trigger,
the trigger then queries the db to populate its the fields.
i.e
******************************************************
TRIGGER trigger1 ON table1
FOR INSERT
AS
DECLARE @id
DECLARE @field1
DECLARE @field2
DECLARE @field3
...
SELECT @field1=f1,@field2=f2,@field3=f3
FROM table2
WHERE id=@id
INSERT INTO table1 VALUES (@field1,@field2,@field3)
******************************************************
i hope thats clearer ....
cheers,
TNT
-
Have you tested your SELECT to make sure it is returning what you want it to return?
In other words, in Query Analyzer, run JUST the SELECT (not the INSERT part). How many rows are returned (make sure you provide any parameters that you would normally provide).
-SQLBill
-
hi SQLBill,
the SELECT statement returns 5 records.
In the trigger: @@ROWCOUNT returns 5 aswell, but only one (last) record inserted in the table
Does this mean all records from the SELECT are passed to the trigger and then the trigger is only executing the last record ???
Could this be a bug (feature!!!)
? or am i doing something wrong?or do i have to loop through the records in the trigger.. if so how do i do that ???
many thanx ...
TNT
-
I agree with SQLBill, if you get bad data in the outputs from the select and try to insert you might be getting an error causing a rollback on the rows somehow.
Also you say you are batching these. How are you batching: DTA, BULK INSERT, BCP, other?
Finally you might consider instead of doing the SELECT then the INSERT something like this
INSERT INTO
table1
VALUES
SELECT
f1,f2,f3
FROM
table2
WHERE
[id]=@id
Might also replace [id]=@id with
[id] IN (SELECT [id] FROM inserted)
and get rid of any cursor you may have. (I am assuming a cursor here since you may have multiple rows and if not then most likely you are getting only the last row of your batch to do the insert which would be your issue).
-
Thinking about it, if you do the following type of code
DECLARE @val INT
SET @val = (SELECT valid FROM tblx)
If multiple rows return you will get an error.
If you however do
DECLARE @val INT
SELECT @val = valid FROM tblx
Then @val will be set to the last valid from tblx that comes thru the buffer.
Do you have cleanup code that runs at the end of your batch that cleans out items with missing data or maybe you are just getting the last record based on your trigger due to the fact the last record is the last thru the buffer if your code is like the secound example.
Thsi would explain what you are seeing.
-
Hi Antares686,
i use the following code:
DECLARE @val INT
SELECT @val = valid FROM tblx
What i mean by 'batch' is
:INSERT INTO table1 SELECT field1 FROM table2
However, when the records are inserted one by one it works..
i dont have any clean up code.
I think your right about the last record thru the buffer. what do you advise to do ???
thanx again for replying.....
TNT
-
A couple of things
1 you use reference to table 2 in and outside you trigger statement and I am kind of confused.
If you are trying to insert the data based on the second table then use the
INSERT INTO table1 SELECT allfieldstogointo1 FROM table2
But if you trigger has other items do like so
CREATE TRIGGER ...
FOR INSERT
AS
SET NOCOUNT ON
INSERT tbl1 (FIELDLIST) SELECT tbl2.relatedfields FROM tbl2 INNER JOIN inserted ON tbl2.[id] = inserted.[id]
This should do what you want but if confused then can you provide a bit more detasils of table 1 and table 2 both in and outside your trigger (in case 1 and 2 are different tables than those referneced outside)
-
Hi Antares686,
Table1,2,3 are something like this...
******************************************************
CREATE TABLE [table1]
(
[id] INTEGER (FK)
[fd1] NVARCHAR
[fd2] NVARCHAR
[fd3] NVARCHAR
)
CREATE TABLE [table2]
(
[id] INTEGER (FK)
[f1] NVARCHAR
[f2] NVARCHAR
[f3] NVARCHAR
)
CREATE TABLE [table3]
(
[id] INTEGER (PK)
)
******************************************************
This is the trigger on table1....
******************************************************
TRIGGER trigger1 ON table1
INSTEAD OF INSERT
AS
DECLARE @id
DECLARE @field1
DECLARE @field2
DECLARE @field3
...
SELECT @id=I.[id] FROM I INSERTED
SELECT @field1=f1,@field2=f2,@field3=f3
FROM table2
WHERE table2.[id]=@id
INSERT INTO table1 VALUES (@id,@field1,@field2,@field3)
******************************************************
From outside i execute the following query (Q1):
******************************************************
INSERT INTO table1 (table1.[id])
SELECT table3.[id] FROM table3
******************************************************
The trigger is only executed for the last record, like you said 'last record in the buffer'.
When i execute the following query(Q2), it works
******************************************************
DECLARE @tempID INTEGER
DECLARE loop CURSOR FOR SELECT table3.[id] FROM table3
OPEN loop
FETCH NEXT FROM loop INTO @tempID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO table1 (table1.ID) VALUES (@tempID)
FETCH NEXT FROM loop INTO @tempID
END
CLOSE loop
DEALLOCATE loop
******************************************************
it also works if i use Q1 and change 'INSTEAD OF INSERT' to 'FOR INSERT' (inside trigger1).
Its just when using Q1 and 'INSTEAD OF INSERT' (inside trigger1) that dont work...
Does this mean i have to take care of the looping through the records inside the trigger ??? how???
many thanx,
TNT
-
Actually you shouldn't have to. ALl the items from the insert batch you do should exist in the inserted table for the event.
Try this
TRIGGER trigger1 ON table1
INSTEAD OF INSERT
AS
INSERT INTO table1
SELECT f1,f2,f3
FROM table2 t2
INNER JOIN
inserted i
ON
t2.[id] = i.[id]
This way you treat the data as a set.
Now in your case if you are always going to opt over the data from 2 as opposed to 3 I would consider using a join for the initial insert and drop the trigger.
INSERT INTO table1
SELECT f1,f2,f3
FROM table2 t2
INNER JOIN
table3 t3
ON
t2.[id] = t3.[id]
Also not sure but you reference [id] from table 3 to insert in table 1 but in your trigger it is used for the join. Do you even need the id, if not then doing the second option means you can drop that column from table 1 but if you do make sure you have it in the right place of your join to keep in either circumstance.
-
i will change the trigger structure so that i 'treat the data as a set', as u said..
am still curious why the trigger is not executing for every record !!!
thanx alot for ur help..
TNT
-
Triggers fire per transaction not per record. WIth the INSERT...SELECT the whole piece is one transaction which means all the items are in the inserted table at one time when the trigger fires. As I said with the @var = column in a select the @var reads in each value as the buffer is read (in this case every record in the inserted table) and becuase you are saying @var = column the last one thru will be what value you have for the next step. Havign a little fun with this try this.
DECLARE @var VARCHAR(8000)
SELECT @var = ISNULL(@var + ',','') + cast([id] as varchar(10)) from table3
SELECT @var
What happens with the above is the previous value is saved and the next concatinated on.
So if 3 only contained values 1, 2 and 3 your output for @var will be
1,2,3
Seeing this effect should help you understand what happened.
-
coool, thanx for the tip
if i remove the 'ISNULL(@var + ',','')' the query returns one value...
my '@var = column ' have WHERE clause which uniquely identify a record..
i gonna examine the trigger litter deeper and see whats excatly happening....
Cheers
TNT
-
That previous post is correct. The AFTER trigger executes only after the entire SQL statement has executed. The INSTEAD OF trigger executes instead of the SQL statement. SQL Server triggers are very different from Oracle triggers, which can fire on every row or on the statement or transaction (or other events?).
Use the INSTEAD OF trigger and re-do the INSERT inside the trigger. INSTEAD OF triggers are not recursive, so the INSERT won't retrigger anything, but, you'll use the temporary tables that are created for you and available inside the trigger (inserted data and deleted data). You'll do something like:
INSERT INTO table1 SELECT col1, col2 FROM <inserted_data>
INSERT INTO table2 SELECT col3, col4 FROM <inserted_data>
...
See http://msdn.microsoft.com/msdnmag/issues/03/12/DataPoints/default.aspx for a very good explanation of triggers, especially about what happens and when during execution of a SQL statement on a table with trigger(s).
That said, it looks as if the trigger may not be necessary for your logic but it is confusing given the names table1, table2, field1, and field2 .
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply