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
