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.
