Trigger not firing ?

[nowandever29]

I have a simple trigger that moves records from the main table to a history table, then move records from the load table into the main table.

This trigger is an INSERT trigger on the load table.

For some reason it's not running??? I t never fires, even when I insert records.

Thanks!

CREATE TRIGGER InsPatchStatus ON TblPatchStatusLoad
FOR INSERT AS
BEGIN

-- This trigger moves new records from the load table to the main
-- table, but first it moves any preexisting records for this server
-- to the history table from the main table, so we will always have
-- one set of records in the main table.

DECLARE @Server varchar(100)
SET @Server = (SELECT TOP 1 Computer FROM INSERTED)

INSERT INTO tbltest VALUES (@Server)

-- Copy old records for this server from the main table to the history table
INSERT INTO TblPatchStatusHist SELECT * FROM tblPatchStatus WHERE Computer = @Server

-- Remove the old records for this server from the main table
DELETE FROM TblPatchStatus WHERE Computer = @Server

-- Copy the new records for this server from the load table to the main table.
INSERT INTO TblPatchStatus SELECT * FROM tblPatchStatusLoad WHERE Computer = @Server

-- Remove the new records for this server from the load tbale.
DELETE FROM TblPatchStatusLoad WHERE Computer = @Server


END

 

[JayKusch]

I am wondering if the trigger is working and the code is not ???

Try adding a line right above ...

INSERT INTO tbltest VALUES (@Server)

that looks like ...

INSERT INTO tbltest VALUES ('TESTME')

Then insert a record, firing the trigger, and see if a record exists in the tbltest w/ a value of 'TESTME'. Then we will know that your trigger is working but the code is not.

Have a feeling the @Server variable is the culprit.

Thanks

J. Kusch

 

[nowandever29]

Yep, I did that. The trigger never fires...

I'm pretty sure the syntax is OK, because if I take the code out and run it in query analyzer, it runs just fine (of course, I have to replace INSERTED with something else).

It appears that the trigger is not firing...I checked, and it IS defined against the table. I should note that there is no primary key or indexes defined any of the tables yet. Could that prevent it from firing?

Thanks!

 

[ESquared]

Have you viewed this trigger in Enterprise Manager instead of Query Analyzer, just to see to verify it's actually attached to the table? Right-click on the table then All Tasks | Manage Triggers | select the trigger name from the dropdown.

Also, in Query Analyzer try putting the owner in front of the table like dbo.TblPatchStatusLoad...

 

[JayKusch]

I beleive for the trigger to work correctly, you need a keyed ID field on your TblPatchStatusLoad table. Lets say for simplicity it is called tblPSL_ID and is set as an Int. Could even set it as an Identity field and let SQL Server insert the next sequential ID key for you.

Then your code may look like ...

SET @Server =

(SELECT i.Computer
FROM INSERTED as i
JOIN TblPatchStatusLoad as psl
ON (i.tblPSL_ID = psl.tblPSL_ID))


Thanks

J. Kusch

 

[nowandever29]

OK, out of sheer frustration I temporarily dumped the history and load tables, and I'm going to just try to define a trigger that removes old records before new ones are inserted. So the trigger looks like:

CREATE TRIGGER InsPatchStatus ON TblPatchStatus
INSTEAD OF INSERT AS
BEGIN

INSERT INTO tbltest VALUES ('Trigger Fired')
DELETE FROM tblPatchStatus WHERE Computer IN (SELECT Computer FROM INSERTED)

END

When I insert records to the table, a big fat nothing happens! No error, no deletions, no record in the test table. Nothing. I tried adding a dummy identity field, and I even made it a primary key. Aughhh!

BTW, I'm running MS SQL 2000 standard edition. The records are inserted by DTS, which is parsing an XML file. The insert goes OK, but the trigger doesn't fire...

Thanks again!

 

[nowandever29]

I found the problem.

In the DTS job, "Fast Load" was turned on. Apparently that disables logging, and also prevents triggers from firing, like a bulk insert.

D'oh!