Trigger unaffected by insertion transaction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Trigger unaffected by insertion transaction

Author

Topic

zakwithu
Starting Member

8 Posts

Posted - 2009-09-30 : 06:57:20

Hi All,
I'm tring to catch records being inserted in a SQL server 2k by a hardcoded software.
I create a trigger in the first database (say db1) to update a table in another database(db2)
both tables are identical(tblA & tblB).

CREATE TRIGGER [Update_db2_tblB] ON db1.dbo.tblA
FOR INSERT
AS
BEGIN
INSERT INTO db2.dbo.tblB
SELECT * from inserted
END

the problem is that i see the db1.dbo.tblA is being filled but db2.dbo.tblB is not updated.
since the software that fills db1.dbo.tblA is hardcoded (not open source) I'm not sure about the way it use to do insert.

1) I thought triggers not enabled in the database but regardless of wheater it is enabled or disabled I enabled it like this:

ALTER TABLE db1.dbo.tblA ENABLE TRIGGER ALL

2) Also I think that software use bulk insert instead of insert.

Q1:what things that might be the reason?
Q2:how can i control triggers when using bulk insert?
Q3: is there any way to insert records without affecting triggers?

any other howto, guidance idea would be highly appreciated.

thank you all

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-30 : 08:01:16

Please do not cross post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133667

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources