Wrong Triggers ? - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Wrong Triggers ?

Author

Topic

hendrasd
Starting Member

20 Posts

Posted - 2002-10-05 : 01:12:52

I created a table Table1 on Database1.

CREATE TABLE Table1
(
PgwId smallint
IDENTITY(1,1),
PgwName varchar(50)
)

And then I created a table Table2 on Database2.

CREATE TABLE Table2
(
PgwId smallint,
PgwAtr varchar(50)
)

I added a trigger, called trInsertPgw, for insert to Table1.

CREATE TRIGGER trInsertPgw
ON dbo.Table1
FOR INSERT
AS
DECLARE @pPgwId int
SELECT @pPgwId = PgwId FROM inserted
INSERT INTO Database2.dbo.Table2 (PgwId)
VALUES (@pPgwId)

I imported several data from a Microsoft Excel 2000 file to Table1. After the import process was completed and success, I opened the Table1 and saw the data I imported. But in the Database2 Table2, I found no PgwId data I imported to Table1.

Any ideas why this is happen ? How to solve it, so that the imported data can also be inserted to the Table2 Database 2 ?

-- Never stop learning --

chadmat
The Chadinator

1974 Posts

Posted - 2002-10-05 : 04:25:51

How did you do the import?

From BOL:
By default, bulk copy operations do not execute triggers.
All bulk copy operations (the BULK INSERT statement, bcp utility, and the bulk copy API) support a bulk copy hint, FIRE_TRIGGERS.

-Chad

hendrasd
Starting Member

20 Posts

Posted - 2002-10-07 : 04:37:27

Hi chadmat. I used DTS Wizard to transform the data. Is it true that the trigger can't be executed if I use the DTS ?

-- Never stop learning --

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-07 : 07:40:20

Triggers require tranaction log entries so it may ell depend on the dts task that is used.

Easiest way is to have an identity on the insert table then you can use an SP to transfer to another table using the identity.

Your trigger seems to asume that entries are added one row at a time so would also depend on the insert mechanism.

Also the PgwId is already in Table1 - why do you need another copy with no other data.
Adding the PgwAtr later would cause a lot of fragmentation - more efficient to use a table which just contains the identity from Table1 which has been processed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources