StoredProcedure with Trigger in Sql Server

Question

Hi How can we define StoredProcedure with Trigger in Sql Server ?

Aarti · Answer

Hi Rajesh, Refer this Syntax: May be it will help you create Trigger Trigger_name After INSERT ON my_table DECLARE v_out number; BEGIN my_updateproc(:new.column1, :new.column2, v_out); :new.column3 := v_out; END; Thanks.

Datta Kharad · Answer

Hi... CREATE OR REPLACE my_procedure (p_variable1 IN my_table.column1%type, p_variable2 IN my_table.column2%type, p_out OUT number) AS BEGIN p_out := p_variable1 + p_variable2; END; / CREATE OR REPLACE TRIGGER my_test_trigger BEFORE INSERT ON my_table DECLARE v_out number; BEGIN my_procedure(:new.column1, :new.column2, v_out); :new.column3 := v_out; END; /

Dhaval Patel · Answer

Dear Rajesh Please Refer below link. Stored Procudure with Trigger This post help you than mark as " Accepted Answer "

Jaganathan Bantheswaran · Answer

Hi Rajes, As per my understading on your need, We can create a trigger inside the SP. We need to use Dynamic SQL as like the below one. USE tempdb; SELECT ProductNumber, ListPrice, Color INTO Product FROM AdventureWorks2008.Production.Product GO CREATE PROC sprocCreateDynamicTrigger AS BEGIN DECLARE @SQL nvarchar(max)= 'CREATE TRIGGER trgProduct on Product for INSERT AS DECLARE @InsProd varchar(32) SELECT @insProd = ''TRIGGER: '' + ProductNumber FROM inserted PRINT @InsProd' EXEC sp_executeSQL @SQL END GO -- Execute stored procedure to create trigger EXEC sprocCreateDynamicTrigger GO INSERT Product VALUES ('Alpha Romeo 2011', 40000, 'Blue') GO -- TRIGGER: Alpha Romeo 2011 DROP PROC sprocCreateDynamicTrigger DROP TABLE tempdb.dbo.Product Source

StoredProcedure with Trigger in Sql Server

Answers (4)