sql触发器在azure弹性数据库中不工作

我已经从azure安装了一个弹性代理。弹性代理在azure中使用db来维护和运行其逻辑。它创建的表列表很长。但这张table正是我要做的。
jobs\u internal.job\u executations（保存弹性作业的每次运行）
dbo.custom\u status（为跟踪执行活动而创建的表）
dml操作将插入dbo.custom\u状态模式的表

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\u internal.job\u上引入触发器时，更新、插入、删除

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 server创建的触发器，并且该触发器不会停止该表的dml操作
因此，我尝试了一个观点
jobs.job使用dml而不是

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操作不会触发触发器。
我的扳机有问题吗。
如果快速触发器触发，它将阻止表上的所有其他操作
第二个触发器从不触发视图。

sql触发器在azure弹性数据库中不工作

1条答案

相关问题

热门标签

最新问答