SQL触发器在Azure弹性DB中不起作用

Question

我已经安排了一个来自Azure的弹性探员。弹性剂使用数据库来维护和运行Azure中的逻辑。它创建的表列表很长。但这张桌子是我要做的。

1. jobs_internal.job_exectutions (保存弹性job)
2. dbo.custom_status (为跟踪执行活动而创建的表)的每次运行)

表，其中DML操作将插入到dbo.custom_status模式中。

CREATE TABLE [dbo].[custom_status](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [job_id] [uniqueidentifier] NOT NULL,
    [job_name] [nvarchar](128) NULL,
    [job_execution_id] [uniqueidentifier] NOT NULL,
    [start_time] [datetime] NULL,
    [end_time] [datetime] NULL,
    [lifecycle] [nvarchar](50) NULL,
    [message] [nvarchar](max) NULL,
    [exception] [nvarchar](max) NULL,
    [column_state] [nvarchar](20) NOT NULL,
    [entry_time] [datetime] NULL,
 CONSTRAINT [PK_dbo.custom_status] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[custom_status] ADD  DEFAULT (getdate()) FOR [entry_time]
GO

在更新后在jobs_internal.job_executions上引入触发器时，插入、删除

CREATE TRIGGER [jobs_internal].[TR_status_message]
ON [jobs_internal].[job_executions]
AFTER INSERT, UPDATE, DELETE
AS
    BEGIN TRY
        DECLARE @activity NVARCHAR(20);

        IF EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED)
        BEGIN
            SET @activity = 'UPDATE';

            INSERT INTO [dbo].[custom_status]
                ([job_id]
               ,[job_name]
               ,[job_execution_id]
               ,[start_time]
               ,[end_time]
               ,[lifecycle]
               ,[message]
               ,[exception]
               ,[column_state])
            SELECT
                org_job_exe.[job_id]
                ,(SELECT name
                FROM jobs_internal.jobs
                WHERE job_id = org_job_exe.job_id)
                ,org_job_exe.[job_execution_id]
                ,org_job_exe.[start_time]
                ,org_job_exe.[end_time]
                ,org_job_exe.[lifecycle]
                ,(SELECT message
                FROM jobs_internal.job_task_executions
                WHERE job_execution_id = org_job_exe.job_execution_id)
                ,(SELECT exception
                FROM jobs_internal.job_task_executions
                WHERE job_execution_id = org_job_exe.job_execution_id)
                ,@activity
            FROM INSERTED as org_job_exe;

            PRINT 'UPDATE operation failed in custom message trigger';
        END;

        IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
        BEGIN
            SET @activity = 'DELETE';

            INSERT INTO [dbo].[custom_status]
                ([job_id]
               ,[job_name]
               ,[job_execution_id]
               ,[start_time]
               ,[end_time]
               ,[lifecycle]
               ,[message]
               ,[exception]
               ,[column_state])
            SELECT
                org_job_exe.[job_id]
                ,(SELECT name
                FROM jobs_internal.jobs
                WHERE job_id = org_job_exe.job_id)
                ,org_job_exe.[job_execution_id]
                ,org_job_exe.[start_time]
                ,org_job_exe.[end_time]
                ,org_job_exe.[lifecycle]
                ,(SELECT message
                FROM jobs_internal.job_task_executions
                WHERE job_execution_id = org_job_exe.job_execution_id)
                ,(SELECT exception
                FROM jobs_internal.job_task_executions
                WHERE job_execution_id = org_job_exe.job_execution_id)
                ,@activity
            FROM DELETED as org_job_exe;

            PRINT 'DELETE operation failed in custom message trigger';
        END;

        BEGIN
            SET @activity = 'INSERT';

            INSERT INTO [dbo].[custom_status]
                    ([job_id]
                   ,[job_name]
                   ,[job_execution_id]
                   ,[start_time]
                   ,[end_time]
                   ,[lifecycle]
                   ,[message]
                   ,[exception]
                   ,[column_state])
                SELECT
                    org_job_exe.[job_id],
                    'test'
                    --,(SELECT name
                    --FROM jobs_internal.jobs
                    --WHERE job_id = org_job_exe.job_id)
                    ,org_job_exe.[job_execution_id]
                    ,org_job_exe.[start_time]
                    ,org_job_exe.[end_time]
                    ,org_job_exe.[lifecycle]
                    ,'test'
                    --,(SELECT message
                    --FROM jobs_internal.job_task_executions
                    --WHERE job_execution_id = org_job_exe.job_execution_id)
                    ,'test'
                    ,@activity
                FROM INSERTED as org_job_exe;

            PRINT 'INSERT operation failed in custom message trigger';
        END;
    
        
    END TRY
    BEGIN CATCH
        PRINT 'Custom message trigger failed';
        THROW;
    END CATCH;

触发器工作，但停止了基表上的所有其他CRUD操作/ DML操作，导致弹性作业无法工作，并堆积在队列中。

该表上已经存在由ElasticJob服务器创建的触发器，这并不会停止表的DML操作。

因此，我尝试了一下

jobs.job_executions与DML of代替

ALTER TRIGGER [jobs].[TR_job_alert]
ON  [jobs].[job_executions]
    INSTEAD OF UPDATE
    AS
        BEGIN
            --just to test normal is being triggered
            insert custom_status
            values (NEWID(), 
            'testing', 
            NEWID(), 
            null, 
            null, 
            'test', 
            null, 
            null, 
            'test', 
            GETDATE());

            --INSERT INTO [dbo].[custom_status]
            --  ([job_id]
            --   ,[job_name]
            --   ,[job_execution_id]
            --   ,[start_time]
            --   ,[end_time]
            --   ,[lifecycle]
            --   ,[message]
            --   ,[exception]
            --   ,[column_state])
            --SELECT
            --  org_job_exe.[job_id]
            --  ,org_job_exe.[job_name]
            --  ,org_job_exe.[job_execution_id]
            --  ,org_job_exe.[start_time]
            --  ,org_job_exe.[end_time]
            --  ,org_job_exe.[lifecycle]
            --  ,org_job_exe.[last_message]
            --  ,null
            --  ,@activity
            --FROM inserted as org_job_exe

            --BEGIN
            --  SET @activity = 'INSERT';

            --  INSERT INTO [dbo].[custom_status]
            --      ([job_id]
            --     ,[job_name]
            --     ,[job_execution_id]
            --     ,[start_time]
            --     ,[end_time]
            --     ,[lifecycle]
            --     ,[message]
            --     ,[exception]
            --     ,[column_state])
            --  SELECT
            --      org_job_exe.[job_id]
            --      ,(SELECT name
            --      FROM jobs_internal.jobs
            --      WHERE job_id = org_job_exe.job_id)
            --      ,org_job_exe.[job_execution_id]
            --      ,org_job_exe.[start_time]
            --      ,org_job_exe.[end_time]
            --      ,org_job_exe.[lifecycle]
            --      ,(SELECT message
            --      FROM jobs_internal.job_task_executions
            --      WHERE job_execution_id = org_job_exe.job_execution_id)
            --      ,(SELECT exception
            --      FROM jobs_internal.job_task_executions
            --      WHERE job_execution_id = org_job_exe.job_execution_id)
            --      ,@activity
            --  FROM inserted as org_job_exe
         --END;
        END;
GO

视野上的扳机永远不会开火！我知道少量的批量DML操作不会触发触发器。

我的扳机有什么问题吗。

1. 快速触发器如果触发，它将阻止表
2. 上的所有其他操作，而第二个触发器永远不会在视图上触发。

Answer 1

尝试删除打印语句。希望渺茫，但可能是因为它们造成了阻碍未来处理的悬念。