Create trigger in stored procedure

Question

I am trying to create a trigger inside stored procedure as it will be removed when the table is dropped.

I am getting an error and am not able to execute it.

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

Here is how you create your trigger inside a stored procedure using dynamic SQL, which is the only way to do it.

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;