Is the DTS job using Bulk Instert to Import data? If so, this could be your
issue (SEE BOL):
Trigger Execution
All bulk copy operations (the BULK INSERT statement, bcp utility, and the
bulk copy API) support a bulk copy hint, FIRE_TRIGGERS. If FIRE_TRIGGERS is
specified on a bulk copy operation that is copying rows into a table, INSERT
and INSTEAD OF triggers defined on the destination table are executed for
all rows inserted by the bulk copy operation. By default, bulk copy
operations do not execute triggers.
These considerations apply to bulk copy operations that specify
FIRE_TRIGGERS:
Bulk copy operations that would usually be minimally logged are fully
logged.
Triggers are fired once for each batch in the bulk copy operation. The
inserted table passed to the trigger contains all of the rows inserted by
the batch. Specify FIRE_TRIGGERS only when bulk copying into a table with
INSERT and INSTEAD OF triggers that support multiple row inserts.
No result sets generated by the insert triggers are returned to the client
performing the bulk copy operation.
-----Original Message-----
From: Justin Gilli [mailto:[Email Address Removed] Tuesday, November 25, 2003 9:54 AM
To: LazyDBA.com Discussion
Subject: RE: Need Help With A Trigger
I tested the trigger on another table and inserted some test data and it
works.
I have a DTS job importing the data with "Delete Rows in destination table".
Perhaps this is causing the issue.
Justin
-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed] Tuesday, November 25, 2003 11:30 AM
To: Justin Gilli
Subject: RE: Need Help With A Trigger
Here is an other way you may want to try:
Create trigger[WhseInsert} on dbo.Costing
For insert
AS
Declare @whsecode varchar(5),
@docnumber int,
Select @whsecode = whsecode from inserted
Select @ItemNumber = itemNumber from inserted
If(@WhseCode ='QON')
begin
Update[Costing]
Set WhseCode = 'ZZZ'
Where ItemNumber in (@ItemNumber)and Whsecode = @whsecode
End
-----Original Message-----
From: Justin Gilli [mailto:[Email Address Removed] November 25, 2003 11:23 AM
To: LazyDBA.com Discussion
Subject: RE: Need Help With A Trigger
A good suggestion with the Begin..End but this did not solve it. I have used
variations on this If statement in triggers before with success. Is there
another method that would work for this case?
-----Original Message-----
From: Beal, Jeremy [mailto:[Email Address Removed] Tuesday, November 25, 2003 11:15 AM
To: Justin Gilli; LazyDBA.com Discussion
Subject: RE: Need Help With A Trigger
Create trigger[WhseInsert} on dbo.Costing
For insert
AS
If(select WhseCode from inserted)='QON'
begin
Update[Costing]
Set WhseCode = 'ZZZ'
Where ItemNumber in (select ItemNumber from inserted)
End
Should it have a begin-end for this if statement?
I don't think this solves the problem due to my not knowing if the IF
statement itself actually works
Jeremyb
-----Original Message-----
From: Justin Gilli [mailto:[Email Address Removed] Tuesday, November 25, 2003 8:05 AM
To: LazyDBA.com Discussion
Subject: Need Help With A Trigger
I am attempting a trigger on a table. Data for this table is being imported
from another db. I want the trigger to replace the imported warehouse code
with a different code.
I have this so far but it is not working:
Create trigger[WhseInsert} on dbo.Costing
For insert
AS
If(select WhseCode from inserted)='QON'
Update[Costing]
Set WhseCode = 'ZZZ'
Where ItemNumber in (select ItemNumber from inserted)
Syntax checks out but nothing happens.
Advice much appreciated.
Regards,
Justin
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Get today's
cartoon: http://www.LazyDBA.com To unsubscribe, e-mail:
mssqldba-[Email Address Removed] For additional commands, e-mail:
mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Get today's
cartoon: http://www.LazyDBA.com To unsubscribe, e-mail:
mssqldba-[Email Address Removed] For additional commands, e-mail:
mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Get today's cartoon: http://www.LazyDBA.com
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page