sql-server - 在存储过程中创建触发器

Question

我正在尝试在存储过程中创建一个触发器，因为它会在删除表时被删除。

我遇到错误，无法执行。

CREATE PROCEDURE EC5Trigger2
AS
  CREATE TRIGGER trVendorProductsPST
  ON VendorProducts
  FOR INSERT
  AS
  BEGIN
    SET NOCOUNT ON

    DECLARE  @SKU VARCHAR(64)
    SELECT @SKU = I.SKU FROM inserted AS I

    INSERT INTO ProductStockTransactions (SKU, stockingCode)
    VALUES (@SKU, 'A')
  END

  RETURN 0

Answer 1

下面是如何使用动态 SQL 在存储过程中创建触发器，这是唯一的方法。

CREATE PROCEDURE EC5Trigger2
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @TriggerCode NVARCHAR(max);

  SET @TriggerCode = 'CREATE TRIGGER trVendorProductsPST
  ON VendorProducts
  FOR INSERT
  AS
  BEGIN
    SET NOCOUNT ON
    DECLARE @SKU VARCHAR(64)
    SELECT @SKU = I.SKU from INSERTED as I

    INSERT INTO ProductStockTransactions (SKU, stockingCode)
      VALUES (@SKU, ''A'')
  END';

  EXEC(@TriggerCode);

  RETURN 0;
END