January 27, 2011 at 7:12 pm
Hello All
I am having problem wit a trigger which i really cant understand.
I am use this trigger to insert into a table when a new customer has been added to the database
Below is the trigger
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER
TRIGGER [dbo].[Customer_InsertSync] ON [dbo].[Customer] FOR INSERT
AS
INSERT INTO Sync_Table( Record_Id,TableName,Operation,Transfered,LocationCode,BranchCode)
Select I.CustomerCode,'Customer','I',0,I.LocationCode,I.BranchCode
From Inserted I
The problem is its not consistent or I should say it does not fire sometimes.
If fifteen customers are added for the day about five are not added to the Sync_Table.
Can someone tell me where I am going wrong I really cant see anything wrong with this trigger
Thanks in advance
January 27, 2011 at 7:46 pm
I suggest reading this blog post by Steve Jones
You will have to scroll down quite aways, for the title
"Common SQL Server Mistakes – Multi Row DML Triggers" dated Sept 23, 2010
Which contains this sample code:
Create trigger orders_update_inventory on orders
for update
as
select @qty = a.qty - b.qty
from inserted a
inner join deleted b
on a.orderid = b.orderid
select @product = productid from inserted
update inventory
set onhand = onhand - ( a.qty - b.qty)
from inserted a
inner join deleted b
on a.orderid = b.orderid
where inventory.productid = i.productid
/* return Triggers should always be written to handle multiple rows,
using the inserted and deleted tables for joins instead of variables.
Even if you always just update single rows, coding this way will
prevent issues if there is a multiple row change. */
January 28, 2011 at 7:37 am
I don't see anything in the OP's code that doesn't take multi-row DML into account.
The only thing I can think is that triggers do not fire for BULK INSERT processes by default...
January 28, 2011 at 8:55 am
The trigger simply selects from "inserted". That'll handle multi-row inserts.
I would suspect constraint/nullability violations, or explicit/implicit rollbacks.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply