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
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;
insertedto the target tableI am trying to save trigger in SP as it will be removed when the table is dropped.the simplest solution is to save the create trigger in file. After you re-create the table, just execute the create trigger scriptINSERTthat causes this trigger to fire inserts 25 rows, you'll get the trigger fired once and theInsertedpseudo table will contain 25 rows. Which of those 25 rows will your code select fromInserted? It's non-deterministic, you'll get one arbitrary row and you will be ignoring all other rows. You need to rewrite your trigger to take this into account!