Calling a procedure from Trigger?

With a small demonstration we will see how we can call a stored procedure from a trigger.

Here we are creating a table Employee

create table tbl_Employee

(id int identity(1,1),

name varchar(20))

Command(s) completed successfully.

Now lets insert couple of records into newly created table.

insert into tbl_Employee

select 'Akansha'  union all

select 'Aurpita'

Since our table is ready, we can now create a procedure, which we will be calling inside a trigger.

create procedure sp_DisplayEmployee

as

       select * from tbl_Employee

go

Command(s) completed successfully.

At this point, we have everything ready, table, procedure, so now turn to create trigger on table Employee for Insert and Update operation and call sp_Display function in it.

We have very simple logic inside trigger i.e. whenever any insert or update commands fired on Employee table, this trigger will comes into picture and display the original data in the table.

create trigger trg_Employee

on tbl_Employee

for insert,update

as

       exec sp_DisplayEmployee

go

Command(s) completed successfully.

Our trigger is now ready, lets fire update statement to see whether it will fire associated trigger.

update tbl_Employee

set name = 'Lopamudra'

where id =4

id     name

1      Akansha

2      Aurpita

Great, so with update statement, it fires trigger and in return trigger call sp_DisplayEmployee stored procedure to display records.

Conclusion: You can call procedure inside trigger.

Get your washroom ready for your coding :)

Post Reference: Vikram Aristocratic Elfin Share