最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

sql - Create trigger after insert when system insert data row by row - Stack Overflow

programmeradmin5浏览0评论

I try created trigger after other system insert data to table, but system insert data row by row, it make my trigger run many time and low performance, how to fix it:

USE DRAFT
GO

CREATE OR ALTER TRIGGER trg_recreate_table_trigger_kpi_secured_loan
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    -- Capture event data
    DECLARE @eventData XML = EVENTDATA();
    DECLARE @objectName NVARCHAR(255) = @eventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)');

    -- Check if the created table is 'test_target_2025_nguyennn'
    IF @objectName = 'test_target_2025_nguyennn'
    BEGIN
        PRINT 'Table test_target_2025_nguyennn has been created.';

        -- Dynamically create an AFTER INSERT trigger on the new table
        DECLARE @createTriggerSQL NVARCHAR(MAX) = '
            CREATE OR ALTER TRIGGER trg_after_insert_test_target_2025_nguyennn
            ON test_target_2025_nguyennn
            AFTER INSERT
            AS
            BEGIN
                SET NOCOUNT ON;

                -- Drop and recreate draft..test_nguyennn with inserted data
                BEGIN 
                    IF OBJECT_ID(''draft..test_nguyennn'', ''U'') IS NOT NULL
                    BEGIN
                        DROP TABLE draft..test_nguyennn;
                    END

                    SELECT *
                    INTO draft..test_nguyennn
                    FROM test_target_2025_nguyennn;
                END

            END;
发布评论

评论列表(0)

  1. 暂无评论