I need help with my trigger - 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

General SQL Server Forums

New to SQL Server Programming

I need help with my trigger

Author

Topic

Cwm
Starting Member

39 Posts

Posted - 2008-12-31 : 22:11:32

Hi All,
This is my first time at attempting to create a trigger...
I want the trigger to run when this stored procedure is called...
PROCEDURE sp_Copy$Completed$Order$In$Bulk

AS
bulk insert TESTDB
from 'C:\LOST\FormattedTickts1.txt'
with
(
--maxerrors = 0,
--keepidentity,
fieldterminator = ',',
rowterminator = '\n'
)
RETURN

I tried doing this trigger but it doesn't work when i run the bulk insert...here is the trigger i made...

create trigger tginsDuplicates on [TESTDB]
for insert
as
select distinct TicketNumber into Newtable from [TESTDB]
delete T1
from [TESTDB] T1, [TESTDB] T3
where T1.TicketNumber = T3.TicketNumber
and T1.COID > T3.COID

I have no idea what i am doing wrong, to me it looks as though it should run when an insert happens on the TESTDB.

Could someone tell me what I am doing wrong?

Thanks in advance.

Chris

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-12-31 : 22:35:39

>>I want the trigger to run when this stored procedure is called...
Then why not just put the trigger logic in the same stored procedure?

The trigger has several problems. NewTable will be created the first time it runs (assuming it doesn't already exist). But the next time it runs you'll get an error because it already exists. Perhaps you want INSERT INTO <existingTable> SELECT... rather than SELECT INTO <newTable>...

The trigger code is not using the virtual table [inserted] which is available inside a trigger so it will always act on the entire table rather than what was inserted.

I'll assume the actual DELETE statement is doing what you intend.

You should use the newer JOIN style rather than a comma seperated list of tables.

Finally, what specifically "didn't work"? errors, no rows, wrong rows, or what?

Be One with the Optimizer
TG

Cwm
Starting Member

39 Posts

Posted - 2008-12-31 : 22:54:33

Hi TG,
I see what you are saying about the NewTable, what i should have added in the trigger was to drop the newtable first ( because i know for a fact that there will be a new table called newtable.
As for the newer join style instead of the comma seperated, is that like an inner join?

I did some further reading on triggers and bulk inserts, apparently i need to use the FIRE_TRIGGERS in my bulk insert statement to make sure the trigger runs, because i guess this has to be done when using a bulk insert statement..
As for the errors, it showed as no rows affected. I am going to add the FIRE_TRIGGERS to my bulk insert and recreate my trigger and see how that goes.

Thanks

Chris

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-01 : 13:05:50

yup...thats true. unless you set FIRE_TRIGGERS the trigger code wont be executed for bulk insert. this is specified in books online

Cwm
Starting Member

39 Posts

Posted - 2009-01-01 : 13:38:38

So visakh16,

Is this how it would look in my stored procedure then?

PROCEDURE sp_Copy$Completed$Order$In$Bulk

AS
bulk insert TESTDB
from 'C:\LOST\FormattedTickts1.txt'
with
(
--maxerrors = 0,
--keepidentity,
fieldterminator = ',',
rowterminator = '\n',
FIRE_TRIGGERS
)
RETURN

Or is there more to add to the fire triggers?

Thanks

Chris

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 01:11:43

yup. it looks fine. did you test this?

Cwm
Starting Member

39 Posts

Posted - 2009-01-02 : 10:39:57

Yes I did, I tested it last night. It worked perfectly.

Thanks

Chris

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 10:54:19

welcome