Trigger not firing - 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

Transact-SQL (2000)

Trigger not firing

Author

Topic

watherton
Starting Member

6 Posts

Posted - 2004-09-14 : 08:45:40

Hi guys, can someone lend me a hand a figuring out why my trigger is not working.

here is the trigger code:
CREATE TRIGGER [TDataDump]
ON [dbo].[DataDump]
for update
AS
Declare @HalNum char(10), @OldHalNum char(10), @ProductDesc char(50), @Vendor char(10), @VendorNum char(10), @Brand char(10), @PackType char(10), @CutterReq char(10), @PhotoReq char(10), @Barcode char(10)

Select @HalNum = HalNum from inserted
Select @OldHalNum = OldHalNum from inserted
Select @ProductDesc = ProductDesc from inserted
Select @Vendor = Vendor from inserted
Select @VendorNum = VendorNum from inserted
Select @Brand = Brand from inserted
Select @CutterReq = CutterReq from inserted
Select @PhotoReq = PhotoReq from inserted
Select @Barcode = Barcode from inserted

insert [dbo].[ProjectLines] values(@HalNum,@OldHalNum,@ProductDesc,@Vendor,@VendorNum,@Brand,@PackType,@CutterReq,@PhotoReq,@Barcode)

The data is inserted into the DataDump table from a dts package.

Cheers

Wayne

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-14 : 08:49:54

If the data in inserted, this won't work. You need a FOR INSERT trigger. Also, if you are inserting multiple rows, this trigger isn't going to work anway. You would need something like this:


CREATE TRIGGER TDataDump

ON dbo.DataDump 

FOR INSERT

AS

INSERT ProjectLines(
	HalNum,
	OldHalNum,
	ProductDesc,
	Vendor,
	VendorNum,
	Brand,
	CutterReq,
	PhotoReq,
	Barcode)

	SELECT
		HalNum,
		OldHalNum,
		ProductDesc,
		Vendor,
		VendorNum,
		Brand,
		CutterReq,
		PhotoReq,
		Barcode
	FROM inserted

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

watherton
Starting Member

6 Posts

Posted - 2004-09-14 : 10:56:44

Derrick thanks for that, the trigger works fine when I use upate ProjectLines(HalNum)values('1') but does nothing when my code enters the data.

I created a dts package and the saved it as .bas file, converted that file into c#, this code works because it enters the data into the datadump table, but does not fire off the trigger.

any suggestions?

wayne

watherton
Starting Member

6 Posts

Posted - 2004-09-14 : 11:09:17

Derrick this is well wierd. My converted dts code works fine (except that it does not fire off the trigger). The dts package run from with Enterprise Manager works and also fires off the trigger. aarrrhhh, is this a security issue - I am using impersonation in order to execute the dts code.

well strange

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources