Can we call stored procedure in trigger in sql server?

Question: Can we call stored procedure in trigger in sql server?

Answer  : Yes

Question: Can we call trigger inside stored procedure in sql server?

Answer  : No, it is automatically executed when we perform DML operation on table.

Let’s explore it.

Step 1: Create table

CREATE TABLE TABLE1

(

       ID INT IDENTITY,

       NAME VARCHAR(50)

)

CREATE TABLE TABLE2

(

       ID INT IDENTITY,

       NAME VARCHAR(50)

)

Step 2: Create insert procedure

CREATE PROC spINSERT

@NAME VARCHAR(50)

AS

BEGIN

       INSERT INTO TABLE2(NAME) VALUES(@NAME)

END

Step 3: Create update procedure

CREATE PROC spUPDATE

@ID INT,

@NAME VARCHAR(50)

AS

BEGIN

       UPDATE TABLE2

       SET  NAME = @NAME

       WHERE ID = @ID

END

Step 4: Create delete procedure

CREATE PROC spDELETE

@ID INT

AS

BEGIN

       DELETE FROM TABLE2

       WHERE ID = @ID

END

Step 5: Create insert trigger

CREATE TRIGGER TRG_INSERT

ON TABLE1 FOR INSERT

AS

BEGIN

       -- Using Query

       --INSERT INTO TABLE2(NAME) SELECT NAME FROM INSERTED

       -- Using Store procedure

       DECLARE @NAME VARCHAR(50)

       SELECT @NAME = NAME FROM INSERTED 

       EXEC spINSERT @NAME

END

Step 6: Create update trigger

ALTER TRIGGER TRG_UPDATE

ON TABLE1 FOR UPDATE

AS

BEGIN

       DECLARE @ID VARCHAR(50)

       DECLARE @NAME VARCHAR(50)

       SELECT @ID = ID, @NAME = NAME FROM INSERTED 

       -- Using Query

       --UPDATE TABLE2 SET NAME = @NAME WHERE ID = @ID

       -- Using Store procedure

       EXEC spUPDATE @ID, @NAME

END

Step 7: Create delete trigger

ALTER TRIGGER TRG_DELETE

ON TABLE1 FOR DELETE

AS

BEGIN

       DECLARE @ID VARCHAR(50)

       SELECT @ID = ID FROM DELETED 

       -- Using Query

       --DELETE FROM TABLE2 WHERE ID = @ID

       -- Using Store procedure

       EXEC spDELETE @ID

END

Step 8: insert record in table1

INSERT INTO TABLE1(NAME)VALUES('A')

GO 2

Step 9: Select the both table record, you will see 2 rows added in both the table, in table2 record added by trigger fired on table1.  

SELECT * FROM TABLE1

ID          NAME   

----------- --------

1           A

2           A

(2 row(s) affected)

SELECT * FROM TABLE2

ID          NAME

----------- --------

1           A

2           A

(2 row(s) affected)

Step 10: update the record of table1, whose id is 2, in table2 record updated by trigger fired on table1.  

UPDATE TABLE1 SET NAME = 'B' WHERE ID = 2

Step 11: Select the both table record, you will see updated rows in both the table

SELECT * FROM TABLE1

ID          NAME   

----------- --------

1           A

2           B

(2 row(s) affected)

SELECT * FROM TABLE2

ID          NAME

----------- --------

1           A

2           B

(2 row(s) affected)

Step 12: delete the record of table1, whose id is 2, in table2 record deleted by trigger fired on table1.  

  

DELETE FROM TABLE1 WHERE ID = 2

Step 13: Select the both table record

SELECT * FROM TABLE1

ID          NAME   

----------- --------

1           A

(1 row(s) affected)

SELECT * FROM TABLE2

ID          NAME

----------- --------

1           A

(1 row(s) affected)

DROP TABLE TABLE1

DROP TABLE TABLE2

Note:

I have commented the query line in all trigger, you simply uncomment the line and comment the store procedure execution line.

If both line are open in trigger the query fire twice.

For more information visit following link