当表中发生更改时，触发器会触发SP并更新从属表 [英] Trigger fires an SP when there is a change in the table and updates the dependent table

问题描述

Hi Team， 

这里我给出了一个触发器的代码，当对表进行更改并对依赖表进行必要的修改时触发该代码 

触发器是：  trg_ct_test_drugs

SP是：  usp_performs_update_insert_delete

SP是根据参数为我工作正常，但问题在于触发器。 

我试过插入。它只插入一种类型的"abc"。我的意思是说插入了abc的记录而不是其他记录。 

如果您有任何输入要更正，请帮助我。 

这里是触发器： 

CREATE TRIGGER [dbo]。[trg_ct_test_drugs] ON table1

INSERT INSERT

       ，更新，删除

AS

BEGIN



        - 循环变量

        DECLARE @i INT = 0

        DECLARE @i_update INT = 0

        DECLARE @i_delete INT = 0

        - 循环的行数

        DECLARE @count INT

        DECLARE @Count_updated INT

        DECLARE @Count_deleted INT

        - 这些是循环变量。

        DECLARE @v_abc_id INT

        DECLARE @v_abc VARCHAR（14）

        DECLARE @v_test_drug BIT

        DECLARE @v_abc_somedrug NUMERIC

        DECLARE @v_mme_some_factor NUMERIC

        DECLARE @v_start_date日期

        DECLARE @v_end_date日期

        DECLARE @v_username VARCHAR（75）

        DECLARE @key CHAR（1）



        BEGIN

              - 获取已删除行的数量

              SELECT @Count_deleted = Count（d.abc_id）

              FROM删除d

              WHERE d.abc_id NOT IN（

                SELECT abc_id

                FROM插入

  &NBSP ;            ）



              - Loop_Delete

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; WHILE @i_delete< @Count_deleted

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; BEGIN

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - 通过abc_id获取药物

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SELECT TOP 1 @v_abc_id = d.abc_id

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; FROM删除d

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; NBSP&; WHERE d.abc_id NOT IN（

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ; SELECT abc_id

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; FROM插入<登记/>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP）



  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - 删除当前行的值

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;选择@v_abc = d.abc

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_test_drug = d.test_drug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_abc_somedrug = d.abc_somedrug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_mme_some_factor = d.mme_some_factor

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_start_date = d.start_date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_end_date = d.end_date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_username = d.update_user

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ key ='D'

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; FROM删除d

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;在哪里d.abc_id = @v_abc_id

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;及d.abc_id NOT IN（

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP; SELECT i.abc_id

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ; FROM插入的I

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP）

  EXEC usp_performs_update_insert_delete @v_abc

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_test_drug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_abc_somedrug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_mme_some_factor

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_start_date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_end_date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_username

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ key

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - 设置下一次迭代

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SET @i_delete = @i_delete + 1

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; END - 结束Loop_Update



  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - 获取更新的行数量
  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SELECT @Count_updated = Count（d.abc_id）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; FROM删除d

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; INNER JOIN插入i开启d.abc_id = i.abc_id



  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - Loop_Update

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;什么时候@i_update< @Count_updated

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; BEGIN

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - 通过abc_id获取药物

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SELECT TOP 1 @v_abc_id = d.abc_id

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; FROM删除d

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; INNER JOIN插入i开启d.abc_id = i.abc_id

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;订购d.abc_id ASC



  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - 更新当前行的值

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;选择@v_abc = d.abc

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_test_drug = d.test_drug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_abc_somedrug = d.abc_somedrug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_mme_some_factor = d.mme_some_factor

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_start_date = d.start_date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_end_date = d.end_date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_username = d.update_user

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ key ='U'

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; FROM删除d

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; INNER JOIN插入i开启d.abc_id = i.abc_id

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;在哪里d.abc_id = @v_abc_id



  &NBSP; EXEC usp_performs_update_insert_delete @v_abc

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_test_drug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_abc_somedrug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_mme_some_factor

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_start_date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_end_date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_username

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ key

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - 设置下一次迭代

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SET @i_update = @i_update + 1

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; END - 结束Loop_Update



  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - 获取更改/插入的行数▼
  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SELECT @Count = Count（abc_id）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; FROM插入



  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - Loop_1

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; WHILE @i< @count

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; BEGIN

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - 通过abc_id获取药物

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SELECT TOP 1 @v_abc_id = abc_id

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; FROM inserted

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;订购BY abc_id ASC



  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - 填充当前行的值

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;选择@v_abc = abc

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_test_drug = test_drug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_abc_somedrug = abc_somedrug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_mme_some_factor = mme_some_factor

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_start_date = [start_date]

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_end_date = end_date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_username = create_user

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ key ='我'
  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; FROM已插入

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;在哪里abc_id = @v_abc_id



  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; EXEC usp_performs_update_insert_delete @v_abc

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_test_drug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_abc_somedrug

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_mme_some_factor

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_start_date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_end_date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ v_username

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，@ key



  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; - 设置下一次迭代

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SET @i = @i + 1

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;结束--while_1

  &NBSP; &NBSP;   END - 陈述

END --trigger

解决方案

为什么你需要一个循环逻辑？

为什么不重写proc
usp_performs_update_insert_delete&NBSP;&NBSP; 以在其上得到了更新的整个记录​​批次工作/插入？

Hi Team, 

Here i'm giving the code of a trigger which fires when there is a change to the table and do necessary modifications to the dependent table. 

Trigger is: trg_ct_test_drugs

SP is: usp_performs_update_insert_delete

SP is working fine for me based on the parameters but the problem is with the trigger. 

I tried insert. It is inserting only one type of the "abc". I means records having abc are inserted not other records. 

Please help me if you have any inputs to correct this. 

Here is the trigger: 

CREATE TRIGGER [dbo].[trg_ct_test_drugs] ON table1
AFTER INSERT
       ,UPDATE, DELETE

AS
BEGIN

       -- Looping variable
       DECLARE @i INT = 0
       DECLARE @i_update INT = 0
       DECLARE @i_delete INT = 0
       -- Number of rows for looping
       DECLARE @count INT
       DECLARE @Count_updated INT
       DECLARE @Count_deleted INT
       -- These are the looping variables.
       DECLARE @v_abc_id INT
       DECLARE @v_abc VARCHAR(14)
       DECLARE @v_test_drug BIT
       DECLARE @v_abc_somedrug NUMERIC
       DECLARE @v_mme_some_factor NUMERIC
       DECLARE @v_start_date DATE
       DECLARE @v_end_date DATE
       DECLARE @v_username VARCHAR(75)
       DECLARE @key CHAR(1)

       BEGIN
              -- Get number of Deleted rows
              SELECT @Count_deleted = Count(d.abc_id)
              FROM deleted d
              WHERE d.abc_id NOT IN (
                             SELECT abc_id
                             FROM inserted
                             )

              -- Loop_Delete
              WHILE @i_delete < @Count_deleted
              BEGIN
                      -- Get drugs by abc_id
                      SELECT TOP 1 @v_abc_id = d.abc_id
                      FROM deleted d
                      WHERE d.abc_id NOT IN (
                                    SELECT abc_id
                                    FROM inserted
                                    )

                      -- Delete values for the current row
                      SELECT @v_abc = d.abc
                             ,@v_test_drug = d.test_drug
                             ,@v_abc_somedrug = d.abc_somedrug
                             ,@v_mme_some_factor = d.mme_some_factor
                             ,@v_start_date = d.start_date
                             ,@v_end_date = d.end_date
                             ,@v_username = d.update_user
                             ,@key = 'D'
                      FROM deleted d
                      WHERE d.abc_id = @v_abc_id
                             AND d.abc_id NOT IN (
                                    SELECT i.abc_id
                                    FROM inserted i
                                    )
  EXEC usp_performs_update_insert_delete @v_abc
                             ,@v_test_drug
                             ,@v_abc_somedrug
                             ,@v_mme_some_factor
                             ,@v_start_date
                             ,@v_end_date
                             ,@v_username
                             ,@key
                      -- Set up next iteration
                      SET @i_delete = @i_delete + 1
              END --End Loop_Update

              -- Get number of updated rows
              SELECT @Count_updated = Count(d.abc_id)
              FROM deleted d
              INNER JOIN inserted i ON d.abc_id = i.abc_id

              -- Loop_Update
              WHILE @i_update < @Count_updated
              BEGIN
                      -- Get drugs by abc_id
                      SELECT TOP 1 @v_abc_id = d.abc_id
                      FROM deleted d
                      INNER JOIN inserted i ON d.abc_id = i.abc_id
                      ORDER BY d.abc_id ASC

                      -- Update values for the current row
                      SELECT @v_abc = d.abc
                             ,@v_test_drug = d.test_drug
                             ,@v_abc_somedrug = d.abc_somedrug
                             ,@v_mme_some_factor = d.mme_some_factor
                             ,@v_start_date = d.start_date
                             ,@v_end_date = d.end_date
                             ,@v_username = d.update_user
                             ,@key = 'U'
                      FROM deleted d
                      INNER JOIN inserted i ON d.abc_id = i.abc_id
                      WHERE d.abc_id = @v_abc_id

    EXEC usp_performs_update_insert_delete @v_abc
                             ,@v_test_drug
                             ,@v_abc_somedrug
                             ,@v_mme_some_factor
                             ,@v_start_date
                             ,@v_end_date
                             ,@v_username
                             ,@key
                      -- Set up next iteration
                      SET @i_update = @i_update + 1
              END --End Loop_Update

              -- Get number of changed/inserted rows
              SELECT @Count = Count(abc_id)
              FROM inserted

              -- Loop_1
              WHILE @i < @count
              BEGIN
                      -- Get drugs by abc_id
                      SELECT TOP 1 @v_abc_id = abc_id
                      FROM inserted
                      ORDER BY abc_id ASC

                      -- Populate values for the current row
                      SELECT @v_abc = abc
                             ,@v_test_drug = test_drug
                             ,@v_abc_somedrug = abc_somedrug
                             ,@v_mme_some_factor = mme_some_factor
                             ,@v_start_date = [start_date]
                             ,@v_end_date = end_date
                             ,@v_username = create_user
                             ,@key = 'I'
                      FROM Inserted
                      WHERE abc_id = @v_abc_id

                      EXEC usp_performs_update_insert_delete @v_abc
                             ,@v_test_drug
                             ,@v_abc_somedrug
                             ,@v_mme_some_factor
                             ,@v_start_date
                             ,@v_end_date
                             ,@v_username
                             ,@key

                      -- Set up next iteration
                      SET @i = @i + 1
              END --while_1
       END --statements
END --trigger

解决方案

Why do you need a looping logic for this?

Why not rewrite the proc usp_performs_update_insert_delete  to work in batches over entire records which got updated/inserted?

这篇关于当表中发生更改时，触发器会触发SP并更新从属表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！