I am trying to call a procedure using a trigger, so whenever a MASTER_ID is updated in one table, my procedure should be triggered, but if the Master ID is added for the first time, it should not be added.
I have written my procedure as below and is working fine, but not sure how to connect it to trigger and do I need to change the master_id variable value. Please help
My Master ID should be the id same as it has been updated.
CREATE TRIGGER ABC
AFTER UPDATE
ON CED FOR EACH ROW WHEN (OLD.MASTERID <> NEW.MASTERID AND OLD.ORIGINDATE = NEW.ORIGINDATE AND OLD.STATUS <> NEW.STATUS)
BEGIN
EXE dbo.someproc
END;
CREATE PROCEDURE [dbo].[PROC NAME]
(
@trans_id INT,
@return_flag VARCHAR(1) OUTPUT,
@error_msg VARCHAR(100) OUTPUT
)
AS
BEGIN
BEGIN TRY
DECLARE @MASTER_ID INT = NULL;
DECLARE @INFO VARCHAR(10) = NULL;
DECLARE @AP VARCHAR(20) = NULL;
DECLARE @AV VARCHAR(20) = NULL;
SET @return_flag = 'Y';
SELECT
@MASTER_ID = MASTER_ID,
@INFO = INF
@AP = AP,
@AV = AV,
FROM
[dbo].Tablename
WHERE
MASTER_ID = @trans_id;
IF EXISTS (
SELECT 1
FROM [dbo].[table name] AS FM
JOIN [dbo].[2ndtable] AS FP ON FM.MASTER_ID = FP.MASTER_ID
WHERE FM.MASTER_ID = @MASTER_ID
)
BEGIN
SET @STATUS = 'SEEYOU';
END
BEGIN
SET @STATUS = 'SEEYOU';
END
INSERT INTO dbo.table (
[COl1],
[COl2],
[COl3],
[COl4]
)
VALUES (
@MASTER_ID,
@INFO,
@AP,
@AV,
);
SET @error_msg = 'Success';
END TRY
BEGIN CATCH
DECLARE @err_msg VARCHAR(200)
DECLARE @ErrorNumber INT = ERROR_NUMBER()
DECLARE @ErrorMessage NVARCHAR(100) = ERROR_MESSAGE()
SET @err_msg = CONCAT(' ', 'ERROR', ' ', @ErrorNumber, ' ' + @ErrorMessage)
SET @return_flag = 'N';
SET @error_msg = @err_msg;
END CATCH
END
New contributor
VBK is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
5