MQ Series Broker Service vs SQL Server Trigger

Hello

A rather frustrating trigger problem.
When I insert a record manually or even with an insert command, my trigger works fine and executes a job.

The trigger is supposed to work after a MQ Series broker service has added a row to the table but nothing happens.

Does this sound familiar?

The trigger:

CREATE TRIGGER CHECKFORNEWFILES
ON dbo.MQLog
AFTER INSERT
AS

SELECT 1

SET CONCAT_NULL_YIELDS_NULL OFF

DECLARE @.Job_Name AS VARCHAR(50)
DECLARE @.Division AS VARCHAR(50)
DECLARE @.filename AS VARCHAR(50)

-- The bitmask is: power(2,(4-1)) = 8 => ((COLUMNS_UPDATED( )& 8 )> 0)
IF (COLUMNS_UPDATED() & 2 > 0)
BEGIN
UPDATE MQLOG
SET READY = GETDATE() WHERE Updated IS NULL

SELECT @.Division = CASE RTRIM(LTRIM(UPPER(LIBRARY)))
WHEN 'AEB' THEN 'SSS'
WHEN 'AIB' THEN 'ZZZ'
ELSE 'UNKNOWN'
END, @.filename = filename
FROM INSERTED

SELECT @.Job_Name= @.Division + '_' + @.filename
FROM INSERTED

-- Let's execute the job according to the file name which is ready for download.
EXEC msdb..sp_start_job_mq @.Job_Name

UPDATE MQLOG
SET PROCESSED = GETDATE(), UPDATED = 1 WHERE UPDATED IS NULL

END

Many thanks!!

Worf

Changing the AFTER INSERT into FOR INSERT did the trick. Whilst looking in SQL Profiler, we saw an exec sp_execute but not quite an insert, that's why an AFTER INSERT did not work.

regards,

Steve